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
 General SQL Server Forums
 New to SQL Server Programming
 Help on transpose code in a loop
 New Topic  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
52325 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
52325 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
2223 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
2223 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
52325 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  
 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.06 seconds. Powered By: Snitz Forums 2000