Group By - 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

Group By

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-04-02 : 10:58:41

Hi to All, I have a View on Sql 2008 that gives me all the monthly sales of item by customer and my problem is that gives me one row per month and I need all the months horizontally :

Actual Results :

|DebCode| Debtor | ItemCode | ItemDescription |01|02|03|04|05|06|
| 100 |Debtr100| Item-001 | ItemDesc-001 | 1| | | | | |
| 100 |Debtr100| Item-001 | ItemDesc-001 | | 5| | | | |
| 101 |Debtr101| Item-001 | ItemDesc-001 | 4| | | | | |
| 101 |Debtr101| Item-001 | ItemDesc-001 | | 3| | | | |

And I Need Something like this :

|DebCode| Debtor | ItemCode | ItemDescription |01|02|03|04|05|06|
| 100 |Debtr100| Item-001 | ItemDesc-001 | 1| 5| | | | |
| 101 |Debtr101| Item-001 | ItemDesc-001 | 4| 3| | | | |

My actual Code is This :

SELECT * FROM dbo.View_VK_ORDERS_SUMARY_F
PIVOT (SUM(Sold) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) AS QTY
ORDER BY Debtor, Year,Mes,ItemCode ASC

Thanks in advance...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-02 : 11:15:27

[code]SELECT *
FROM (
SELECT DebCode, Debtor, ItemCode, ItemDescription, Year, Month, Sold
FROM dbo.View_VK_ORDERS_SUMARY_F
) AS ORD
PIVOT
(
SUM(Sold) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
) AS QTY
ORDER BY Debtor, Year,Mes,ItemCode ASC[/code]

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