August 27, 2002 at 8:26 am
does anyone have a a T-SQL stored proc that outputs a dynamic cross tabbed recordset?
when I say dynamic, I mean that the stored proc can't use a case statement to handle each value/column. I have seen examples where the values that are to become columes are limited to 1,2,3,4. the stored proc then has a case 1 and then a case 2...etc
I do not know what the potential values are and therfore need a more dynamic approach.
if you could provide the skeleton of a dynamic T-SQL crosstab query, that would really make my day.
thanks,
Guy
August 27, 2002 at 11:45 am
I'm not sure exactly what you want to do, but here is a CROSS-TAB example I have. Now this example does not work when data values are missing for some column values, but possible you can tailor it to your needs. Hope this help give you ideas.....
CREATE TABLE T1
(
projid int NOT NULL,
year char(4) NOT NULL,
amt int not null
)
insert into t1 values(1,'1999',1)
insert into t1 values(1,'2000',2)
insert into t1 values(1,'2001',3)
insert into t1 values(2,'1999',3)
insert into t1 values(2,'2000',2)
insert into t1 values(2,'2001',1)
GO
DECLARE @S AS VARCHAR(8000); SET @S = ''
declare @n as varchar(10)
DECLARE years CURSOR FOR
SELECT distinct year from T1
OPEN years
FETCH NEXT FROM years INTO @n
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @S = @S + ' ' + year FROM T1
where @n = year
group by year
FETCH NEXT FROM years INTO @n
end
print 'PROJID ' + @S
CLOSE years
DEALLOCATE years
DECLARE projid CURSOR FOR
SELECT distinct projid from T1
OPEN projid
FETCH NEXT FROM projid INTO @n
WHILE @@FETCH_STATUS = 0
BEGIN
set @S = ''
SELECT @S = @S + ' ' + cast(amt as char(4))FROM T1
where @n = projid
group by projid, year, amt
FETCH NEXT FROM PROJID INTO @n
print @S
end
CLOSE projid
DEALLOCATE projid
drop table t1
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 27, 2002 at 2:19 pm
thx for the input
here is a stored proc that seemed to do the trick for me
I got it from --> http://www.swynk.com/sqlscripts/misc7-2.asp
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
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
*/
CREATE Procedure sp_Query_Pivot
@Base_Data_SQL varchar(2000), -- Table to use as recordsource to build final crosstab qry
@Row_Headings varchar(200), -- Comma-separated list of rows to use as groupings of data
@Column_Heading varchar(200), -- 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
AS
Declare @SQL varchar(2000),
@Summary_SQL varchar(2000) -- 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(200),
@Pivot_SQL varchar(200) -- 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
-- release objects
DROP TABLE #Col_Heads
-- Finish SQL
Set @SQL = @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
-- Done
Print @SQL
Exec (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply