Samples For Rows to Column


 
 ----Sample1

create table test ( id int identity(1,1),  val varchar(50))
Insert into test values ('A'),('B'),('C') ,('E') ,('D')
SELECT distinct (SELECT  '' + val FROM test FOR XML PATH('')) AS vals

FROM test t
drop table test   



--Sample2

create table test (CATEGORY char(1),   EMAIL varchar(50))
Insert into test values 
('A','Smith@somewhere.com')
,('C','Jones@somewhere.com')
,('A','Bob@somewhere.com')
 ,('C','John@somewhere.com')
 ,('C','Tom@somewhere.com')


SELECT CATEGORY, STUFF( (SELECT  ';' + EMAIL FROM test AS t1
               WHERE      t1.CATEGORY = t1.CATEGORY FOR XML PATH('')), 1, 1, '') AS Emails

FROM test t Where CATEGORY='C'
GROUP BY CATEGORY 


drop table test       
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s