SQL Server 2012: Dynamic crosstab in stored procedure

Question

Im trying to use a stored procedure to create a pivot table between two declared variables @rvar (as rowvariable) and @cvar (as columnvariable). The point is to call the stored procedure from VBA using these two as dynamic input when executing the stored procedure.

My code has three parts:

creating test-data
declaring locals
finding names of columns in crosstab an storing in new local @sql1
executing crosstable with the pivotfunction using the names stored in @sql1.

My problem: The code below works but I would like to make it dynamic regarding the variable defining the column structure - currently set to "q10_1_resp" - so that I only have to declare the local @cvar and use that in part 3 (like in part 4). I have succeeded in making part 3 into a sql-string with subsequent execution but then the column names stored in @sql1 cannot be used in the code in part 4 (I guess it is a scope thing).

--Part 1

create table [user].[test]
    (rowvar nvarchar(max),
    q10_1_resp int,
    q10_2_resp int)
    GO 

INSERT [user].[test]
        VALUES ('PH',1,2),
            ('PH',2,3),
            ('EA',1,5),
            ('EA',5,4),
            ('PH',3,4),
            ('PH',6,6),
            ('EA',4,1),
            ('PH',5,3),
            ('PH',2,1)
        GO

-- Part 2
declare @rvar as nvarchar(max) = 'rowvar'
declare @cvar as nvarchar(max) = 'q10_1_resp' --this input should be dynamic as well
declare @sql1 as nvarchar(max)= ''
declare @sql2 as nvarchar(max)= ''

-- Part 3
select @sql1 = @sql1 + [a].[col] + char(44)
    from
        (select distinct QUOTENAME(q10_1_resp) as [col]
        from [user].[test]
        group by q10_1_resp) as a

SET @sql1 = left(@sql1, len(@sql1) - 1)

-- Part 4
SET @sql2 = 'select ' +
    + @rvar + ','
    + @sql1 
    + ' from (Select '
    + @rvar + ', ' 
    + @cvar
    + ' from [user].[test]) sq pivot(count(' 
    + @cvar 
    + ') for '
    + @cvar + ' IN (' 
    + @sql1 
    + ')) as pt'

exec sp_executesql @sql2

Sample data and expected results we can run your question against will help us help you. There are plenty of examples of Dynamic Pivots on SO; do any of those not help? — Thom A, Dec 17 '19 at 15:33
Also, what you have here isn't a Cross Tab, it's a `PIVOT` query. Although the 2 achieve the same result, a Cross Tab is far less restrictive. — Thom A, Dec 17 '19 at 15:34
The examples does not help because, as I see it, you still have to hardcode the column-name in the stored procedure (the thing I would like to avoid by making it a local). — Uddannelseszoom SFU, Dec 17 '19 at 15:45
What example? You haven't linked to any; or responded to any of my comments. — Thom A, Dec 17 '19 at 15:56
Like this example [https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query] - Here you still have to manually type the "c.category". Or is my point unclear? — Uddannelseszoom SFU, Dec 18 '19 at 09:01
So you want to dynamically pivot on a dynamic column...? There's still no sample data or expected results, so I can't help you here. — Thom A, Dec 18 '19 at 09:08

score 0 · Answer 1 · answered Jan 03 '20 at 13:43

After a great deal of trying to globalize the scalarvariable without success using a temporary table to store the string was the key. The temporary table created at the beginning of the stored procedure can be assigned and referenced the entire time of the procedure. Thus assigning within execution of @sql and then referencing the string at execution of @sql2. I hope it makes sense.

CREATE PROCEDURE [dbo].[sp_crosstab] 
    -- Add the parameters for the stored procedure here
    @rvar nvarchar(max) = '',
    @cvar nvarchar(max) = '',
    @data nvarchar(max) = '',
    @sql nvarchar(max) = '',
    @sql2 nvarchar(max) = '',
    @sql3 nvarchar(max)=''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

create table #temp_crosstab
(
   sqlstr nvarchar(max)
)

set @sql ='
declare @sql1 nvarchar(max) = char(00)
select @sql1 = @sql1 + [a].[col] + char(44)
    from
        (select distinct QUOTENAME(' + @cvar + ') as [col]
        from ' + @data + '
        group by ' + @cvar + ') as a

    SET @sql1 = left(@sql1, len(@sql1) - 1)


insert into #temp_crosstab values (@sql1)'

execute sp_executesql @sql


select @sql3 = [sqlstr] from #temp_crosstab

set @sql2 = '

select ' + @rvar + char(44) +
        @sql3 + 'from (Select '
        + @rvar + char(44) + ' ' 
        + @cvar
        + ' from ' + @data + ') sq pivot(count(' 
        + @cvar 
        + ') for '
        + @cvar + ' IN ('+@sql3+')) as pt'


exec sp_executesql @sql2
    END
GO

SQL Server 2012: Dynamic crosstab in stored procedure

1 Answers1