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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on transpose code in a loop
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Noejfah
Starting Member

South Africa
5 Posts

Posted - 05/28/2013 :  03:25:45  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 05/28/2013 :  04:00:57  Show Profile  Reply with Quote

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
Go to Top of Page

Noejfah
Starting Member

South Africa
5 Posts

Posted - 05/28/2013 :  04:30:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/28/2013 :  05:04:46  Show Profile  Reply with Quote
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

Edited by - visakh16 on 05/28/2013 05:05:29
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 05/28/2013 :  05:05:17  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 05/28/2013 :  05:18:23  Show Profile  Reply with Quote
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
Go to Top of Page

Noejfah
Starting Member

South Africa
5 Posts

Posted - 05/28/2013 :  05:26:11  Show Profile  Reply with Quote
Thank you Visakh it works. I had to tweek it a little bit BUt it works now THANKS YOU!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/28/2013 :  06:02:01  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000