pivot infor - 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

General SQL Server Forums

New to SQL Server Programming

pivot infor

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-03-31 : 12:32:22

Hi everyone, I'm still learning about Pivot tables,right now I have a code that shows me the information from a fully year (from current month to the last year month,from march-to march) as I need it, but I need to change it from vertically to horizontally, exemple :

CURRENT RESULTS
|DebCode|Debtor|ItemCode|ItemDescription| Date |Sold |
|1000000|Deb100|item-001|item-descript-1|201403|05.00|
|1000000|Deb100|item-001|item-descript-1|201402|02.00|
|1000001|Deb101|item-002|item-descript-2|201403|01.00|

RESULTS I need
|DebCode|Debtor|ItemCode|ItemDescription|201403|->|201303|
|1000001|Deb100|item-001|item-descipt-01| 05.00 |->|01.00 |

This is My Actual Code :

SELECT     TOP (100) PERCENT debcode, Debtor, ItemCode, ItemDescription, Year * 100 + Month AS Fecha, Sold
FROM         dbo.View_VK_ORDERS_SUMARY_F
GROUP BY debcode, Debtor, ItemCode, ItemDescription, Year, Month, Sold
ORDER BY Fecha DESC

Can You help me?

Thanks in advance...

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-01 : 00:51:38

SELECT TOP (100) PERCENT debcode, Debtor, ItemCode, ItemDescription,[2014-03-01],[2013-03-01]
FROM (SELECT * FROM dbo.View_VK_ORDERS_SUMARY_F
GROUP BY debcode, Debtor, ItemCode, ItemDescription, Year, Month, Sold)x
PIVOT(MAX(Sold) FOR Date in ([2014-03-01],[2013-03-01])) AS pvt

Veera

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-04-01 : 01:31:17

Hi Veera, the problem is that the headers will be changing every month, by exemple, tomorrow will be from april 2014 to april 2013 (201404,201403,201402,201401,201312,201311,201310,201309,201308,201307,201306,201305,201304),every time I execute the code I receive the current month to the same month but of the last year, I'm lost.

Thanks a lot for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-01 : 01:33:18

you will need to use Dynamic SQL http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources