Dynamic Pivot Table

I have the following table with the details as shown below in the example. Example: Table: test create table test ( cola varchar(10), colb varchar(10), colc varchar(10) ); Inse

Solution 1:

Is it a different issue with this?

DECLARE @Stuff varchar(max) = 'A1,A2,A3'
        DECLARE @Sql varchar(max)
DECLARE @totalparam varchar(max) = len(@Stuff) - len(replace(@Stuff,',',''))+1
DECLARE @colcList varchar(max) = REPLACE(@Stuff,',',''',''')
        SET @Sql = 'SELECT cola,' +@Stuff+ '
                    FROM
                    (SELECT a.cola, a.colc from test a JOIN (
                        SELECT cola, COUNT(InList) AS InList, COUNT(TOTAL) AS TOTAL FROM (
                               SELECT cola, 
                                     CASE WHEN colc IN ( '''+@colcList+''') THEN
                                         1
                                      END AS InList, 
                                      1 AS TOTAL FROM test
                                group by cola, colc) AS tester group by cola) b  ON a.cola = b.cola
                        WHERE b.TOTAL = '+@totalparam+' AND b.InList = b.TOTAL
                        )p
                PIVOT
                (
                    COUNT(colc)
                    FOR colc IN ('+@Stuff+')
                )AS pvt'
    PRINT(@colcList)
    PRINT(@Sql)
    EXEC(@Sql)