How to Pivot SQL data? - SQL Server Forums

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)?

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

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]

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources