CODEWITHSUNDEEP
sql-servert-sqlrows
Maddy.Shik
Maddy.Shik

Reputation: 6787

SQL server: convert rows to columns

I have a 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 a single record containing sales for for each column(month).

Upvotes: 8

Views: 23286

Answers (5)

Dalex
Dalex

Reputation: 3625

To easily transpose columns into rows with its names you should use XML. In my blog I was described this with example: Link

Upvotes: 0

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

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

Upvotes: 10

John Gibb
John Gibb

Reputation: 10773

Here's an alternate way to write the pivot that gives you a little more control (especially over the column names). It's also a little easier to generate dynamic SQL for.

It's similar to Robin's answer, but has the advantage of only hitting the table once:

select
  Sales1 = sum( case when Month = 1 then Sales end )
, Sales2 = sum( case when Month = 2 then Sales end )
, Sales3 = sum( case when Month = 3 then Sales end )
-- etc..
from SalesTable;

I did some investigation, and it seems like the new pivot operator is just syntax sugar for this type of query. The query plans end up looking identical.

As an interesting aside, the unpivot operator seems to also just be syntax sugar. For example:

If you have a table like:

Create Table Sales ( JanSales int, FebSales int, MarchSales int...)

You can write:

 select unpivoted.monthName, unpivoted.sales
 from Sales s
 outer apply (
    select 'Jan', JanSales union all
    select 'Feb', FebSales union all
    select 'March', MarchSales
 ) unpivoted( monthName, sales );

And get the unpivoted data...

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

You can do it with OLAP. Here is another link to MSDN documentation on the topic.

With OLAP, you can create a cube with the information you have, with the layout you need.

If you do not want to go that way, you will have to create summary tables with .NET, Java, TransacSQL, or your preferred language to manipulate SQLServer data.

Upvotes: 1

Robin Day
Robin Day

Reputation: 102458

Not pretty... but this works well

SELECT
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 1) [Sales1],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 2) [Sales2],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 3) [Sales3],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 4) [Sales4],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 5) [Sales5],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 6) [Sales6],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 7) [Sales7],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 8) [Sales8],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 9) [Sales9],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 10) [Sales10],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 11) [Sales11],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 12) [Sales12]

Upvotes: 2

Related Questions