Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to Pivot SQL data?

Author  Topic 

LEOx037
Starting Member

12 Posts

Posted - 2013-06-19 : 16:49:32
I have the following data and I need to pivot it as noted below:

SystemID MarketCode
1 6200
1 6202
1 6526
1 6593
2 7287
3 7474
3 1688
4 1686
5 1886
6 6200
6 6201
7 6202
7 6204
7 6526


End result:

1,6200,6202,6526,6593
2,7287
3,7474,1688
4,1686
5,1886
6,6200,6201
7,6202,6204,6526


Any help would be helpful. Thanks in advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 17:19:53
Are you trying to get the data as single column of comma-separated values, or are you trying to pivot (i.e., get one row for each SytemID with as many columns as there are rows for each SystemId in the original table)?
Go to Top of Page

LEOx037
Starting Member

12 Posts

Posted - 2013-06-19 : 17:27:43
I guess you can say single row per grouping. e.g.
Col1 Col2
1 6200
1 6202
1 6526
1 6593

to look like this: 1,6200,6202,6526,6593

With a comma delimiter
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 17:46:06
[code]SELECT
CAST(a.SystemId AS VARCHAR(32)) + b.MarketIds AS YourSingleColumn
FROM
(SELECT DISTINCT SystemId FROM YourTable) a
CROSS APPLY
(
SELECT ','+CAST(MarketCode AS VARCHAR(32))
FROM YourTable b
WHERE b.SystemId = a.SystemId
ORDER BY b.MarketCode
FOR XML PATH('')
) b(MarketIds);[/code]
Go to Top of Page

LEOx037
Starting Member

12 Posts

Posted - 2013-06-19 : 18:02:12
Thanks James! That's what I am looking for. Learned something new... Cross Apply. :o)
Go to Top of Page
   

- Advertisement -