SQL Server 2008 R2: Pivot table

Meem Published at Dev

Meem

I have the following table:

CREATE TABLE pvt
(
cola varchar(10),
colb varchar(10)
)

insert into pvt values('','2');
insert into pvt values('1','3');
insert into pvt values('9','4');
insert into pvt values('8','5');

Pivot table Query:

DECLARE @StuffColumn varchar(max)
DECLARE @sql varchar(max)

SELECT @StuffColumn = STUFF((SELECT ','+QUOTENAME(cola) 
                        FROM pvt
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@StuffColumn)        

SET @SQL = ' select colb,'+ @StuffColumn +'
         from
         (
            select cola,colb
            from pvt
         )x
         pivot
         (
             count(cola)
             for cola in( '+@StuffColumn +')
         )p'
PRINT(@SQL)
EXEC(@SQL)

Error:

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, 
verify each column has a name. For other statements, look for empty alias 
names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Expected Result:

colb      1  9   8       
-------------------
 2    1   0  0   0
 3    0   1  0   0
 4    0   0  1   0
 5    0   0  0   1

Milen

How about this:

DECLARE @StuffColumn varchar(max)
DECLARE @sql varchar(max)

SELECT @StuffColumn = STUFF((SELECT ','+QUOTENAME(case when cola = '' then 'empty' else cola end) 
                        FROM pvt
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@StuffColumn)        

SET @SQL = ' select colb,'+ @StuffColumn +'
         from
         (
            select case when cola = '''' then ''empty'' else cola end as cola,colb
            from pvt
         )x
         pivot
         (
             count(cola)
             for cola in( '+@StuffColumn +')
         )p'
PRINT(@SQL)
EXEC(@SQL)

You need to give name to your pivot columns.

Solution for more than one empty column + dynamic pivot:

DECLARE @StuffColumn varchar(max)
DECLARE @sql varchar(max)

SELECT @StuffColumn = STUFF((SELECT ','+QUOTENAME(case when cola = '' then Cast(ROW_NUMBER() over (order by colb) as varchar (6))  + 'empty' else cola end) 
                        FROM pvt
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@StuffColumn)        

SET @SQL = ' select colb,'+ @StuffColumn +'
         from
         (
            select   case when cola = '''' then Cast(ROW_NUMBER() over (order by colb) as varchar (6)) +''empty'' else cola end cola,colb
            from pvt
         )x
         pivot
         (
             count(cola)
             for cola in( '+@StuffColumn +')
         )p'
PRINT(@SQL)
EXEC(@SQL)

Demo here

Collected from the Internet

Please contact [email protected] to delete if infringement.