Pages

Search Code Shode

Wednesday, May 26, 2010

MS SQL Server :Convert Rows to Columns

Question:

ihave at table with columns sales(int),month (int) . i want to retrieve sum of sales corresponding to every month .i need ouput in form of 12 columns corresponding to each month.in which there will be single record containing sales for for each column(month)


Solution:


You should take a look at PIVOT for switching rows with columns. This prevents a select statement for each month. Something like this:
DECLARE @salesTable TABLE(
    [month] INT,
    sales INT)
-- Note that I use SQL Server 2008 INSERT syntax here for inserting-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
      ,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
      ,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
      ,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)

SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM(
    SELECT [month], sales
    FROM @salesTable
) AS SourceTable
PIVOT(
    SUM(sales)
    FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

اشتہارات