Needs to list rows as columns and columns as rows

[clive]

I need a query to return the results of something like this:

select
period,
sum(foo),
sum(bar)
group by
period

Except I need each period to be listed as a column not a row. Does anyone know how this is done?

-Clive

 

[TomSark]

You could create a table (or a temp table) that uses the distinct values of the period as a column with something like this:

declare @sqlstr varchar(1000),
@period varchar(30)

create table #mytemptable
(
period varchar(30) -- or whatever type
)

-- save off the future columns into #mytable
insert #mytable
select distinct period
from --whatever table it exists in

declare mycursor cursor for
select distinct period
from #mytable

open mycursor
fetch mycursor into @period

set @sqlstr = "ALTER TABLE #mytemptable "

while @@fetch_status = 0
begin
set @sqlstr = sqlstr + "ADD " + @period + ","
fetch mycursor into @period
end

close mycursor
deallocate mycursor

-- remove the last comma
set @sqlstr = Left( @sqlstr, Len(@sqlstr) - 1)

execute (@sqlstr)

This will create the table with the periods as columns. I'll leave it up to you to code the rest and correct my untested code. The trick is to execute alter table and add the columns (the period values).

I hope this what was what you were looking for... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[swilliams]

This can be achieved using a crosstab query (not sure if it works in SQL Server - I will test after this bachward I know!! - but it works in Access).

1. Create a query to sum the data as you have already, with a slight alteration, needed for the crosstab bit to work:

SELECT Test_Table.Period, Sum(Test_Table.foo) AS Data, "Sum(foo)" AS RowHeader
FROM Test_Table
GROUP BY Test_Table.Period, 1;

UNION ALL SELECT Test_Table.Period, Sum(Test_Table.bar) AS Data, "Sum(bar)" AS RowHeader
FROM Test_Table
GROUP BY Test_Table.Period, 1;


2. Use the query above to base a cross tab query on:

TRANSFORM Sum(qryCrossTabBase.Data) AS SumOfData
SELECT qryCrossTabBase.RowHeader
FROM qryCrossTabBase
GROUP BY qryCrossTabBase.RowHeader
PIVOT qryCrossTabBase.Period;

Simon

 

[swilliams]

I got the first query working (not much to do there), but I could not get anything like a cross tab query to work.

Does anyone know how to do this??
Simon

 

[clive]

I've got the cross-tab query working. I'd couldn't find the TRANSFORM or PIVOT key words anywhere in the Microsoft SQL books online. This may be an Oracle thing.

This is what I had to do:

CREATE TABLE #Temp (
Label1 varchar(50),
Month1 money,
Month2 money,
Month3 money)

INSERT INTO #Temp (Label1, month1)
SELECT SUM(Foo)
FROM Details
WHERE Period = '2000-8-1'
GROUP BY Label

INSERT INTO #Temp (Label1, month2)
SELECT SUM(Foo)
FROM Details
WHERE Period = '2000-9-1'
GROUPO BY Label

INSERT INTO #Temp (Label1, month3)
SELECT SUM(Foo)
FROM Details
WHERE Period = '2000-10-1'
GROUP BY Label

SELECT Label1, SUM(month1), SUM(Month2), SUM(Month3)
FROM #Temp
GROUP BY Label1

It's a bit of a long winded process but it gets the job done. This is what the Books online has to say about cross-tab queries:

Cross-tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year Quarter Amount

---- ------- ------

1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:

Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4

These are the statements to create the Pivot table and populate it with the data from the first table:

USE Northwind

GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total, for example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

You may also want to consider GROUP BY with CUBE or with ROLLUP. Both compute the same sort of information, but in a slightly different format.

(c) 1988-98 Microsoft Corporation. All Rights Reserved.