5.4.08

T-SQL Challenges - Part 2

Query Problem:
Got this problem from MSDN user in microsoft site. Wish to solve this problem.
Is it possible to append the result sets as a single column or result set seperated by comma.
Table Structure
Name Age
venkat 10
arun 20
prabu 30
Output:
venkat,10,arun,20,prabu,30
Sample Query to solve this problem:
Code Snippet

create table VenkatSampleTable(name varchar(100),age int)
insert into VenkatSampleTable values ('venkat',10)
insert into VenkatSampleTable values ('arun',20)
insert into VenkatSampleTable values ('prabu',30)

declare @values as varchar(500)
set @values = ''

select @values = @values + name + ','+cast(age as varchar(50)) + ','from VenkatSampleTable

select substring(@values,1,len(@values)-1)
Its the easiest and fastest way to append the result set.
Happy learning!!!
Regards,
Venkatesan Prabu . J
at

2 comments:

  1. AnonymousJune 13, 2008 at 3:11 AM

    in sql server 2005,
    create table userNames
    (
    Name varchar(50),
    Age int
    )
    insert into userNames values ('ram',10)
    insert into userNames values ('reddy',20)
    insert into userNames values ('dasaradha',30)

    select distinct substring(
    (select + Name + ',' + cast(age as varchar(10))+ ',' from userNames for xml path('')),1,len((select + Name + ',' + cast(age as varchar(10))+ ',' from userNames for xml path('')))-1)
    as NamesandAges from userNames E

    ReplyDelete
  2. Venkatesan PrabuJune 16, 2008 at 4:44 PM

    Thanks for the information. It's really interesting to see the solution with XML path implementation.

    Regards,
    Venkatesan prabu. J

    ReplyDelete