Crosstab query in Sql
-
Is that possible to achieve? I have a table like this:
Product Location Type Day Qty
A StoreA a1 0 2
A StoreB a2 1 4
B StoreA a3 0 1
B StoreB a4 1 5
I'd like to run a select statement that will me:
Product Location Type 0 1
A StoreA a1 2
A StoreB a2 4
B StoreA a3 1
B StoreB a4 5
I normally do a crosstab query in Access to achieve this, but I will be more happy if someone teaches me how to do that in select statement from sqlserver. Thank you very much.
Minh
-
Hey,
SQL might look something like this:
SELECT
Product,
Location,
Type,
day_0 = SUM(CASE WHEN day = 0 THEN qty ELSE 0 END),
day_1 = SUM(CASE WHEN day = 1 THEN qty ELSE 0 END)
FROM
Products
GROUP BY
Product, Location, Type
Regards,
JP
-
Here is the link to a dynamic cross tab query builder that works close to the Access Cross Tab.
http://www.johnmacintyre.ca/codespct.asp
Thanks to Bob Monahon for is answer to my previous question.
-
For easy solutions to all kinds of crosstab/pivoting problems check out the RAC utility.Similar in concept to Access crosstab but much more powerful with many options/features.Fully integrated to sql server as RAC is a system of server sp's and functions.In addition, RAC easily simulates many of the analytic functions that (hopefully) will be in Yukon,ie. row_number().rank,dense_rank etc.This makes RAC an alternative general problem solving tool as analytic functions are much easier to grasp than complicated and convuluted sql89/92.
Note that for crosstabs/pivoting RAC does not use heaps of CASE statements nor CURSORS
RAC v2.2 and QALite @
Check out RAC at:
www.angelfire.com/ny4/rac/ -
we use the following sp to
accomplish this :
/*
Pivot Table stored procedure
Description:
Generates simple pivot tables on data, and outputs the final SQL statement via Print
For example, given data as shown below:
ID year type amt
----------- ----------- ----------- -----------
7 1999 1 23
8 1999 2 44
9 1999 3 55
10 2000 1 66
11 2000 2 77
12 2000 3 88
13 1999 1 11
... you can pivot the data to show the years down the side
and the types across the top...
year 1 2 3 RowTotal
----------- ----------- ----------- ----------- -----------
1999 34 44 55 133
2000 66 77 88 231
... and get the SQL which would produce this table
SELECT Pivot_Data.*,
(Pivot_Data.[1] + Pivot_Data.[2] + Pivot_Data.[3]) AS RowTotal
FROM (SELECT [year],
SUM(CASE [type] WHEN '1' THEN [amt] ELSE 0 END) AS [1],
SUM(CASE [type] WHEN '2' THEN [amt] ELSE 0 END) AS [2],
SUM(CASE [type] WHEN '3' THEN [amt] ELSE 0 END) AS [3]
FROM (select * from zzjunk) AS Base_Data
GROUP BY [year]) AS Pivot_Data
.. by calling the procedure as shown below:
exec sp_Query_Pivot 'select * from zzjunk', '[year]', '[type]',
'Select distinct [type] from zzjunk', 'SUM', '[amt]', 'Y'
-- Base_Data_SQL gets all data from zzjunk
-- group by year
-- use the type column as headings
-- get the list of types available from the junk table,
-- use 'SUM' at each cell
-- sum the '[amt]' column
-- include a summary for the row
Example 2:
sp_Query_Pivot 'select * from fin_ben_allocation as fba inner join bmo_group as bg
on fba.beneficiary_id = bg.bmo_group_id',
'[eng_id]', '[bmo_group_desc_short]',
'Select bmo_group_desc FROM bmo_group', 'SUM', '[avoidance_pct]', 'Y'
Returns:
eng_id PCCG E! PCG IBG Corp RowTotal
----------- ---------------------------------------- -----
1 1 34 0 0 0 35
2 100 0 34 24 12 170
Arguments:
Base_Data_SQL SQL that returns data to be summarized
Row_Headings Comma-separated list of rows to use as groupings of data
Column_Heading Column to use as heading
Column_Head_SQL SQL that returns set of possible column headings
Operation SUM, PRODUCT, etc
Op_Argument Column to use as argument in operation
Steps in Routine:
1. Get list of distinct column headings
2. Looping through column headings, ALTER SQL for pivot
3. Add summary SQL if required
4. Execute
History:
Jeff Zohrab Aug 13, 2001 Initial release
Duncan tamati 09/06/2004 Introduce global table to list all 6 rows into spreadsteet
*/
CREATE Procedure Take2.sp_Query_Pivot
@Base_Data_SQL varchar(6000), -- Table to use as recordsource to build final crosstab qry
@Row_Headings varchar(1200), -- Comma-separated list of rows to use as groupings of data
@Column_Heading varchar(1200), -- Column to use as heading
@Column_Head_SQL varchar(2000), -- SQL that returns set of possible column headings
@Operation varchar(10), -- SUM, PRODUCT, etc
@Op_Argument varchar(200), -- Column to use as argument in operation
@Add_Row_Summary char(1) , -- 'Y' to include summary, 'N' to omit
@type tinyint=0
AS
set nocount on
Declare @SQL varchar(8000),
@Summary_SQL varchar(8000) -- to summarize each row
SET @SQL = 'SELECT ' + @Row_Headings + ', '
Set @Summary_SQL = ''
-- Get list of distinct column headings
CREATE TABLE #Col_Heads
(
Col_ID int identity(1,1),
Col_Head varchar(200) NULL
 
Exec ('INSERT INTO #Col_Heads(Col_Head) ' + @Column_Head_SQL)
-- select * from #Col_Heads -- debug check
-- loop through all columns, build pivot strings
DECLARE @Col_ID_Curr int, -- column being checked
@Col_ID_Old int,
@Curr_Col_Head varchar(8000),
@Pivot_SQL varchar(8000) -- pivot SQL for current column
SELECT TOP 1 @Col_ID_Curr = Col_ID, @Curr_Col_Head = Col_Head
FROM #Col_Heads ORDER BY Col_ID
IF (@Col_ID_Curr IS NOT NULL )
BEGIN
-- dummy value to enter loop
Set @Col_ID_Old = @Col_ID_Curr - 1
WHILE (@Col_ID_Old <> @Col_ID_Curr)
BEGIN
-- print 'Adding pivot line for heading ' + @Curr_Col_Head -- debug check
Set @Pivot_SQL = char(13) + @Operation
+ '(CASE ' + @Column_Heading
+ ' WHEN ''' + @Curr_Col_Head + ''' THEN ' + @Op_Argument
+ ' ELSE 0 END) AS [' + @Curr_Col_Head +']'
Set @SQL = @SQL + ' ' + @Pivot_SQL
-- Add column name to summary list, if required
If (@Add_Row_Summary='Y')
Set @Summary_SQL = @Summary_SQL + 'Pivot_Data.[' + @Curr_Col_Head +']'
-- Get the next column head
-- if there are no more Col_Heads in the table, the select returns
-- no records, and Col_ID_Curr doesn't change (exits the while loop)
Set @Col_ID_Old = @Col_ID_Curr
SELECT TOP 1 @Col_ID_Curr = Col_ID, @Curr_Col_Head = Col_Head
FROM #Col_Heads
WHERE Col_ID > @Col_ID_Curr
ORDER BY Col_ID
-- Add delimiters to lists if this isn't the last column heading
IF (@Col_ID_Old <> @Col_ID_Curr)
Begin
Set @SQL = @SQL + ', '
Set @Summary_SQL = @Summary_SQL + ' + '
End
END
END
--select * from #Col_Heads
-- release objects
DROP TABLE #Col_Heads
-- Finish SQL
if @type=0
Set @SQL = @SQL + char(13) + ' into ##temp FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
else
Set @SQL = 'insert into ##Temp ' + @SQL + char(13) + ' FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
-- If summary requested, add enclosing Summary SQL
If (@Add_Row_Summary='Y')
Begin
Set @SQL = 'SELECT Pivot_Data.*, ' + char(13)
+ ' (' + @Summary_SQL + ') AS RowTotal ' + char(13)
+ ' FROM (' + @SQL + ') AS Pivot_Data'
End
--print @sql
--select * from #Temp
-- Done
Print @SQL
Exec (@SQL)
select * from ##temp
GO
Hope this helps
-
Here is a adhoc Code Generator for Basic Crosstab functionality.
Of course SQL 2005 will now have the PIVOT statement to make this easier!
/*** Template to CODE Generator to Create CROSS-TAB Select Statement ***
*
*
* Author: Dan Collier
*
* Description: Template to take rows with
* common column data or grouping data
* and auto generate script to Transform them
* into Column names and
* grouped aggregates.
*
* Db Engine: SQL Server 2000
* Client: SQL Query Analyzer
*/
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
/* Start with PREFIX Select statement */
SELECT @SQL = N'SELECT [titles].[pub_id] '+char(13)+','
/* Transform Cross Tab. Group Row values to Columns. */
SELECT @SQL = @SQL + N'ISNULL(SUM(CASE WHEN [titles].[type]='''
+RTRIM([titles].[type])+''''+char(13)+
' THEN ISNULL([titles].[price],0) END),0) AS SUM_'+
+RTRIM([titles].[type])+char(13)+','
FROM [pubs].[dbo].[titles]
WHERE LEN(RTRIM([titles].[type])) > 0
GROUP BY [titles].[type]
/* Remove last comma and return */
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-2)+char(13)
/* Add SUFFIX Statements */
SELECT @SQL = @SQL + N'FROM [pubs].[dbo].[titles]'+char(13)+
'GROUP BY [titles].[pub_id]'
/* Generate the T-SQL statement */
SELECT @SQL
/*** ^^^^^^^^ Save the Above to a Template File *.tql ^^^^^^^^^^^ */
GO
-- *** The defaults setup this statement >>>
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
/* Start with PREFIX Select statement */
SELECT @SQL = N'SELECT [titles].[pub_id] '+char(13)+','
/* Transform Cross Tab. Group Row values to Columns. */
SELECT @SQL = @SQL + N'ISNULL(SUM(CASE WHEN [titles].[type]='''
+RTRIM([titles].[type])+''''+char(13)+
' THEN ISNULL([titles].[price],0) END),0) AS SUM_'+
+RTRIM([titles].[type])+char(13)+','
FROM [pubs].[dbo].[titles]
WHERE LEN(RTRIM([titles].[type])) > 0
GROUP BY [titles].[type]
/* Remove last comma and return */
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-2)+char(13)
/* Add SUFFIX Statements */
SELECT @SQL = @SQL + N'FROM [pubs].[dbo].[titles]'+char(13)+
'GROUP BY [titles].[pub_id]'
/* Generate the T-SQL statement */
SELECT @SQL
-- *** Output of the Generated Script >>>
SELECT [titles].[pub_id]
,ISNULL(SUM(CASE WHEN [titles].[type]='business'
THEN ISNULL([titles].[price],0) END),0) AS SUM_business
,ISNULL(SUM(CASE WHEN [titles].[type]='mod_cook'
THEN ISNULL([titles].[price],0) END),0) AS SUM_mod_cook
,ISNULL(SUM(CASE WHEN [titles].[type]='popular_comp'
THEN ISNULL([titles].[price],0) END),0) AS SUM_popular_comp
,ISNULL(SUM(CASE WHEN [titles].[type]='psychology'
THEN ISNULL([titles].[price],0) END),0) AS SUM_psychology
,ISNULL(SUM(CASE WHEN [titles].[type]='trad_cook'
THEN ISNULL([titles].[price],0) END),0) AS SUM_trad_cook
,ISNULL(SUM(CASE WHEN [titles].[type]='UNDECIDED'
THEN ISNULL([titles].[price],0) END),0) AS SUM_UNDECIDED
FROM [pubs].[dbo].[titles]
GROUP BY [titles].[pub_id]
-
Thank you for all of the excellent helps. I tried all of the ways, and they all work great for me.
Minh
-
Hi Guys
I need help desperately. My problem is also the Pivot functionality in sql server 2000
I am trying to convert my Access sql to server. My access code is
TRANSFORM Sum([3rollupall].SumOfSumOfBOND_AMOUNT) AS SumOfSumOfSumOfBOND_AMOUNT
SELECT [3rollupall].SUBURB, [3rollupall].price_band, Sum([3rollupall].SumOfSumOfBOND_AMOUNT) AS [Total Of SumOfSumOfBOND_AMOUNT]into [3rollupall_Crosstab]
FROM [3rollupall]
GROUP BY [3rollupall].SUBURB, [3rollupall].price_band
PIVOT [3rollupall].institution;
Any ideas how i can convert this??
Thanks a million
-
I need to know what do you have in Institution field. In my case, my cross tab query looks like this:
TRANSFORM Sum(a.Q) AS SumOfQ
SELECT a.product, a.location, a.Type
FROM tbl_a a INNER JOIN tbl_b b
where a.location = b.location
GROUP BY a.product, a.location, a.Type
ORDER BY a.location, a.day
PIVOT a.day;
I convert to sql like this:
SELECT a.product, a.location, a.type,
d0 = SUM(CASE WHEN a.day = 0 THEN a.q ELSE null END),
d1 = SUM(CASE WHEN a.day = 1 THEN a.q ELSE null END),
d2 = SUM(CASE WHEN a.day = 2 THEN a.q ELSE null END),
d3 = SUM(CASE WHEN a.day = 3 THEN a.q ELSE null END),
d4 = SUM(CASE WHEN a.day = 4 THEN a.q ELSE null END),
d5 = SUM(CASE WHEN a.day = 5 THEN a.q ELSE null END),
d6 = SUM(CASE WHEN a.day = 6 THEN a.q ELSE null END)
from tbl_a a inner join tbl_b b on a.location = b.location
group by a.product, a.location, a.type
Hope this will help.
Minh
-
I found example given by dtamati very good.but that some bug when running the script with @Add_Row_Summary char='Y'
i also no sure why declare @type tinyint=0, dtamati didn't explain what parameter need to pass in and i assume it always 0.
so i am doing is
change who part of
--finish SQL
if @type=0
Set @SQL = @SQL + char(13) + ' into ##temp FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
else
Set @SQL = 'insert into ##Temp ' + @SQL + char(13) + ' FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
to
if (@Add_Row_Summary='N')
Set @SQL = @SQL + char(13) + ' into ##temp FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
else
Set @SQL = @SQL + char(13) + ' FROM (' + @Base_Data_SQL + ') AS Base_Data '
+ char(13) + 'GROUP BY ' + @Row_Headings
and problem solve when i pass @Add_Row_Summary char='Y' or @Add_Row_Summary char='Y' .
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply