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)

Answer 1

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]

Answer 2

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

Answer 3

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.

Answer 4

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...

Answer 5

I have a table tblUser , there is userId,firstName,LastName,Mobile,.......,QuestionID. Another table it's name tblResults There is questionID,Question,choiceID,choice,.........

EG:

tblUser

userID FirstName LstName Mobile ... ... ... QuestionID ChiceID

001 xx yy 03212 01 01 001 xx yy 03212 02 02 002 xxx yyy 03425 01 02 002 xxx yyy 03425 02 01 003 xxxx yyyy 03429 03 02

003 xxxx yyyy 03429 03 01

tblResults

QuestionID Question ChoiceID Chice .... ....

01 Are you 01 Male 01 Areyou 02 Female 02 From 01 xxxxx

02 FROM 02 yyyyy

I want to get result shown in following table

UserID FirstName LastName Mobile Areyou From

001 xx yy 03212 Male yyyyy 002 xxx yyy 03425 Female xxxxx 003 xxxx yyyy 03429 Female xxxxx