Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

SQL Dynamic CrossTab Stored Procedure (altered version)

by Jeff Smith

create procedure CrossTab2 (@SQL varchar(1000),  

            @PivotCol varchar(100),

            @Summaries varchar(100),

            @GroupBy varchar(100),

            @OtherFields varchar(100) = Null,

            @Debug bit = 0)

AS

 

set nocount on

set ansi_warnings off

 

declare @Vals varchar(8000);

declare @Vals2 varchar(8000);

declare @Vals3 varchar(8000);

declare @tmp varchar(1000);

declare @TotalLen int;

 

set @Vals = '';

set @Vals2 = '';

set @Vals3 = '';

set @TotalLen = len(@SQL) + len(@GroupBy) + Len(ISNULL(@OtherFields,''))

 

set @OtherFields = isNull(', ' + @OtherFields ,'')

 

create table #temp  (Pivot varchar(100))

 

insert into #temp

exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @SQL + ') A')

 

select @tmp =

    replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(Pivot,'''','''''') +

            ''' THEN '),')[', ' END) as [' + Pivot ),

    @TotalLen = @TotalLen + Len(@tmp),

    @Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,

    @Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,

    @Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end

from 
    #Temp
order by 
    Pivot

 

drop table #Temp

if (@Debug=0)

    exec ( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +

                             @Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)

else

    begin

        create table #Temp2 (SQLText Text);

        insert into #Temp2 (SQLText)

        values ('select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +

                                  @Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy);

        select * from #Temp2

    end


set nocount off
set ansi_warnings on

Legacy Comments
Juver
2005-08-23		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
SQLEXPRESS errors :(


Incorrect syntax near the keybord 'Pivot'
Bob Weston
2006-02-16		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
For your debug mode, I found it more handy to replace the 'false' condition of the 'if' statement to:

print ( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)

Rather than inserting to a temp table and selecting the query text out. I'd say that about 2/3 of the query was actually truncated and not returned in the results.

Thanks,

Bob
Stephen Turner
2006-05-17		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
this wasn't quite working for me, and not long enough!!! here's an improved version:

CREATE procedure CrossTab2 (@SQL varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherFields varchar(100) = Null,
@Debug bit = 0)
AS

set nocount on
SET ANSI_WARNINGS OFF

declare @Vals1 varchar(8000)
declare @Vals2 varchar(8000)
declare @Vals3 varchar(8000)
declare @Vals4 varchar(8000)
declare @Vals5 varchar(8000)
declare @tmp varchar(8000)
declare @TotalLen int
declare @SafeLen int

set @SafeLen = 7800

set @Vals1 = ''
set @Vals2 = ''
set @Vals3 = ''
set @Vals4 = ''
set @Vals5 = ''
set @TotalLen = len(@SQL) + len(@GroupBy) + Len(ISNULL(@OtherFields,''))

set @OtherFields = isNull(', ' + @OtherFields ,'')

create table #temp (Pivot varchar(100))

insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @SQL + ') A')

select @tmp =
replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(Pivot,'''','''''') + ''' THEN '),')[', ' END) as [' + Pivot ),
@TotalLen = @TotalLen + Len(@tmp),
@Vals1 = case when @TotalLen between @SafeLen*0 and (@SafeLen*1)-1 then @Vals1 + ', ' + @tmp else @Vals1 end,
@Vals2 = case when @TotalLen between @SafeLen*1 and (@SafeLen*2)-1 then @Vals2 + ', ' + @tmp else @Vals2 end,
@Vals3 = case when @TotalLen between @SafeLen*2 and (@SafeLen*3)-1 then @Vals3 + ', ' + @tmp else @Vals3 end,
@Vals4 = case when @TotalLen between @SafeLen*3 and (@SafeLen*4)-1 then @Vals4 + ', ' + @tmp else @Vals4 end,
@Vals5 = case when @TotalLen between @SafeLen*4 and (@SafeLen*5)-1 then @Vals5 + ', ' + @tmp else @Vals5 end
from
#Temp
order by
Pivot

drop table #Temp

if (@Debug=0) begin
set nocount off
exec ( 'select ' + @GroupBy + @OtherFields +
@Vals1 +
@Vals2 +
@Vals3 +
@Vals4 +
@Vals5 +
' from (' + @SQL + ') A GROUP BY ' + @GroupBy)
set nocount on
end ELSE begin
Print @totallen
Print 'select ' + @GroupBy + @OtherFields
Print @Vals1
Print @Vals2
Print @Vals3
Print @Vals4
Print @Vals5
Print ' from (' + @SQL + ') A GROUP BY ' + @GroupBy

create table #Temp2 (SQLText Text);
insert into #Temp2 (SQLText)
values ('select ' + @GroupBy + @OtherFields + @Vals1 + @Vals2 +
@Vals3 + @Vals4 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy);
select SQLText, DATALENGTH(SQLText) from #Temp2
end

set nocount off
Steve V
2006-07-27		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
This was just what I was looking for. :)
Here is just a little change for SQL Server 2000 or greater, using table variables instead of temp tables.

Create procedure CrossTab2 (@SQL varchar(1000),
@tpivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherFields varchar(100) = Null,
@Debug bit = 0)
AS

set nocount on
set ansi_warnings off

declare @Vals varchar(8000);
declare @Vals2 varchar(8000);
declare @Vals3 varchar(8000);
declare @tmp varchar(1000);
declare @TotalLen int;

set @Vals = '';
set @Vals2 = '';
set @Vals3 = '';
set @TotalLen = len(@SQL) + len(@GroupBy) + Len(ISNULL(@OtherFields,''))

set @OtherFields = isNull(', ' + @OtherFields ,'')

declare @temp table(tpivot varchar(100))

insert into @temp
exec ('select distinct convert(varchar(100),' + @tpivotCol + ') as tpivot FROM (' + @SQL + ') A')

select @tmp =
replace(replace(@Summaries,'(','(CASE WHEN ' + @tpivotCol + '=''' + replace(tpivot,'''','''''') +
''' THEN '),')[', ' END) as [' + tpivot ),
@TotalLen = @TotalLen + Len(@tmp),
@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,
@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,
@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end
from
@Temp
order by
tpivot

if (@Debug=0)
exec ( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +
@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)
else
begin
declare @Temp2 table(SQLText Text);
insert into @Temp2 (SQLText)
values ('select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +
@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy);
select * from @Temp2
end

set nocount off
set ansi_warnings on

Seetha
2006-09-11		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
I have the value for @SQL as this:

'SELECT Practice, CIty, QR_ID, QRQC_Name, Designation, Criticality FROM dbo.SQA_DashBoard_Project_FTE_Details_Refined, vw_SQA_DashBoard_NonQMQC_QRList WHERE QR_ID = QRQC_ID'

I get this error - with @SQL truncated after 128 chars

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELECT Practice, CIty, QR_ID, QRQC_Name, Designation, Criticality FROM dbo.SQA_DashBoard_Project_FTE_Details_Refined, vw_SQA_Das'.

ANy help?

Thanks
Seetha
Sean Dessureault
2008-12-10		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
Keep getting the error

"Msg 156, Level 15, State 1, Procedure CrossTab2, Line 32
Incorrect syntax near the keyword 'Pivot'.", on the 5/17/2006 post.
Dave
2009-02-08		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
I would like to add a WHERE clause to my @SQL to preselect columns using a STRING STARTWITH and ENDWITH range, but the following fails...

EXEC CrossTab2
'SELECT InvMonth, SlsRep, Cast(Value AS MONEY) as Value FROM MAXInvoiceDetails WHERE SlsRep >'2'',
'InvMonth',
'Sum(Value ELSE 0)[]',
'SlsRep',
'0'

What do I need to fix?

Thanks
PaulP
2009-05-08		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
Hi Jeff
I'm trying to get this great sp to work but keep getting the error "Incorrect syntax near ','.
Incorrect syntax near 'A'". I'm running on SQL 2005 and have replaced Pivot with TPIVOT. I can't figure this out and am running out of time. Can you help. Please. Here is my Select input:

SELECT dbo.CalcDate(SalesDate, '2', 'N') AS SalesDate, Category, Isbn, BookTitle, SUM(NetDollars) as SumOfNetDollars, SUM(NetDollars) as SumNetDollars FROM dbo.Sales LEFT JOIN (((Licenses RIGHT JOIN (Categories RIGHT JOIN Books ON Categories.CategoryID = Books.CategoryID) ON Licenses.LicenseID = Books.LicenseID) LEFT JOIN Series ON Books.SeriesID = Series.SeriesID) LEFT JOIN Formats ON Books.FormatID = Formats.FormatID) ON SALES.SalesISBN = Books.BookID LEFT JOIN Customers ON Sales.CustNumber = Customers.CustID LEFT JOIN Channels ON Sales.ChannelID = Channels.ChannelID Where 1 = 1 AND ((Sales.SalesDate >= '6/1/2008' AND Sales.SalesDate <= '8/1/2008')) GROUP BY SalesDate, Category, Isbn, BookTitle

Many thanks
yagava
2009-06-01		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
here with we attacged.pls gothrouh
Jerry
2009-11-25		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
Jeff I am trying to cnstruct a dynamic crosstab query based on the following: Here is sample data
key id comment
112 1 fishing
113 1 reading

I want the data to look like this

id comment1 comment2
1 fishing reading

How should I begin. Is it with a case statement? I do not want to hardcode comment because there are too many scenatios
Joseph German Ocena
2009-12-11		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
It is not working dude!!

My query...

Execute Crosstab2 'Select * from view_sched','TechnicalName','max(ClientName)' ,'ClientName'

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'A'.
Andy
2010-03-24		 re: SQL Dynamic CrossTab Stored Procedure (altered version)
I know this is quite old now but I had the same problem as Joseph

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'A'.

so I had a play with it and this now works for me.

ALTER PROCEDURE CrossTab3 (@SQL VARCHAR(8000),
@tpivotCol VARCHAR(100),
@Summaries VARCHAR(100),
@GroupBy VARCHAR(100),
@OrderBy VARCHAR(100) = NULL,
@OtherFields VARCHAR(100) = NULL,
@Debug bit = 0)
AS
BEGIN

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @Vals VARCHAR(8000);
DECLARE @Vals2 VARCHAR(8000);
DECLARE @Vals3 VARCHAR(8000);
DECLARE @tmp VARCHAR(1000);
DECLARE @TotalLen INT;

SET @Vals = '';
SET @Vals2 = '';
SET @Vals3 = '';
SET @TotalLen = Len(@SQL) + Len(@GroupBy) + Len(ISNULL(@OtherFields,''))

SET @OtherFields = ISNULL(', ' + @OtherFields ,'')

DECLARE @temp table(tpivot VARCHAR(100));
DECLARE @tmpSQL NVARCHAR(4000);

SET @tmpSQL='SELECT DISTINCT CONVERT(VARCHAR(100),' + @tpivotCol + ') AS [tpivot] FROM (' + @SQL + ') AS [tmp]';
INSERT @temp
EXEC sp_executesql @tmpSQL;

SELECT @tmp =
REPLACE(
REPLACE(@Summaries,'(','(CASE WHEN ' + @tpivotCol + '=''' + REPLACE(tpivot,'''','''''') + ''' THEN ')
,')', ' END) AS [' + REPLACE(tpivot,'''','''''')) + ']',
@TotalLen = @TotalLen + Len(@tmp),
@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,
@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,
@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end
FROM @Temp
WHERE [tpivot] <> ''
ORDER BY tpivot

SELECT @tmpSql = 'SELECT ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3
+ ' FROM (' + @SQL + ') AS [tmp] GROUP BY ' + @GroupBy + CASE WHEN @OrderBy IS NULL THEN '' ELSE ' ORDER BY ' + @OrderBy END;

EXEC (@tmpSQL) ;

SET NOCOUNT OFF;
SET ANSI_WARNINGS ON;

END