SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to Pivot SQL data?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LEOx037
Starting Member

12 Posts

Posted - 06/19/2013 :  16:49:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3697 Posts

Posted - 06/19/2013 :  17:19:53  Show Profile  Reply with Quote
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 - 06/19/2013 :  17:27:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3697 Posts

Posted - 06/19/2013 :  17:46:06  Show Profile  Reply with Quote
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);
Go to Top of Page

LEOx037
Starting Member

12 Posts

Posted - 06/19/2013 :  18:02:12  Show Profile  Reply with Quote
Thanks James! That's what I am looking for. Learned something new... Cross Apply. :o)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000