Help on transpose code in a loop

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

General SQL Server Forums

New to SQL Server Programming

Help on transpose code in a loop

Author

Topic

Noejfah
Starting Member

5 Posts

Posted - 2013-05-28 : 03:25:45

Hi Can anyone help I need code o transpose in a loop. THis is what the data looks like:
acc_key vin mth
123 0 201205
456 0 201205
456 0 201206
123 0 201206
456 0 201207
123 NULL 201207
789 0 201208
456 0 201208
123 NULL 201208
123 0 201209
456 1 201209
789 0 201209
456 0 201210
789 1 201210
123 0 201210
456 0 201211
123 0 201211
789 0 201211
456 0 201212
789 1 201212
123 0 201212
123 0 201301
456 1 201301
456 0 201302
123 0 201302
123 0 201303
456 0 201303
456 0 201304
123 0 201304
789 0 201304
/******************/

And this is the output I need:
acc_key 201205 201206 201207 201208 201209
123 0 0 0 0
456 0 0 NULL 0 1
789 0 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 04:00:57

[code]
DECLARE @mthlist varchar(2000)
SET @mthlist = STUFF((SELECT DISTINCT ',[' + CAST(mth AS varchar(6)) + ']' FROM table ORDER BY '[' + CAST(mth AS varchar(6)) FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM (SELECT acc_key, COALESCE(vin,0) AS vin, mth FROM table)t
PIVOT (SUM(vin) FOR mth IN (' + @mthlist + '))p'

--PRINT (@SQL)
EXEC (@SQL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Noejfah
Starting Member

5 Posts

Posted - 2013-05-28 : 04:30:55

Hi Visakh16 I get the following error when I run the code:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 05:04:46

that was a typo


DECLARE @mthlist varchar(2000)
SET @mthlist = STUFF((SELECT DISTINCT ',[' + CAST(mth AS varchar(6)) + ']' FROM table ORDER BY ',[' + CAST(mth AS varchar(6)) ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM (SELECT acc_key, COALESCE(vin,0) AS vin, mth FROM table)t
PIVOT (SUM(vin) FOR mth IN (' + @mthlist + '))p'

--PRINT (@SQL)
EXEC (@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-28 : 05:05:17

SET @mthlist = STUFF((SELECT ',[' + CAST(mth AS varchar(6)) + ']' FROM (SELECT DISTINCT mth FROM table)T1 ORDER BY '[' + CAST(mth AS varchar(6)) FOR XML PATH('')),1,1,'')

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-28 : 05:18:23

Small typo fixed

quote:
Originally posted by visakh16

that was a typo

DECLARE @mthlist varchar(2000)
SET @mthlist = STUFF((SELECT DISTINCT ',[' + CAST(mth AS varchar(6)) + ']' FROM table ORDER BY ',[' + CAST(mth AS varchar(6)) +']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM (SELECT acc_key, COALESCE(vin,0) AS vin, mth FROM table)t
PIVOT (SUM(vin) FOR mth IN (' + @mthlist + '))p'

--PRINT (@SQL)
EXEC (@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

--
Chandu

Noejfah
Starting Member

5 Posts

Posted - 2013-05-28 : 05:26:11

Thank you Visakh it works. I had to tweek it a little bit BUt it works now THANKS YOU!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 06:02:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources