Simplify Dynamic SQL Pivot Table

Question

I have written a Dynamic Pivot Table Query based on the following. Here is a SQL FIDDLE for reference.

CREATE TABLE TestTable1 ([idnumber] INT, [DataTypeId] INT)
GO
INSERT INTO TestTable1 
VALUES (1, 108), (1, 108), (1, 108), (2, 108), 
       (2, 108), (3, 108), (1, 109),(1, 109),
       (1, 110),(2, 110),(1, 111),(4, 108),(4, 108),
       (4, 110),(4, 111)
GO

Here is the Dynamic SQL that I wrote

DECLARE  @SQL  NVARCHAR(MAX), 
@Cols NVARCHAR(MAX),
@ColsP NVARCHAR(MAX)


SELECT @Cols = STUFF((select  ',  
ISNULL([' + CAST([DataTypeId] as varchar(10)) + '], 0) AS ''' + CAST([DataTypeId] as varchar(10)) + ''''
FROM 
(
SELECT [DataTypeId] FROM [TestTable1] 
GROUP BY [DataTypeId] 
HAVING [DataTypeId] <> '' 
) AS d
ORDER BY [DataTypeId] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')

-- /////////////THIS IS WHAT I WANT REMOVED ////////////////////

SELECT @ColsP = STUFF((select  ',  
[' + CAST([DataTypeId] as varchar(10)) + ']'
FROM 
(
SELECT [DataTypeId] FROM [TestTable1] 
GROUP BY [DataTypeId] 
HAVING [DataTypeId] <> '' 
) AS d
ORDER BY [DataTypeId] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')

-- /////////////////////////////////////////////////////////////

SET @SQL = 'SELECT idnumber,' + @Cols + ' 
FROM 
(SELECT  idnumber, COUNT([DataTypeId]) AS Total, [DataTypeId] FROM [TestTable1] 
GROUP BY  idnumber, [DataTypeId] 
HAVING [DataTypeId] <> ''''
) p
PIVOT
(
SUM(Total) FOR [DataTypeId] IN (' + @ColsP + ')
) AS pvt 
ORDER BY pvt.idnumber'


-- print @SQL 
EXECUTE( @SQL)

I get the result that I want:

| IDNUMBER | 108 | 109 | 110 | 111 |
|----------|-----|-----|-----|-----|
|        1 |   3 |   2 |   1 |   1 |
|        2 |   2 |   0 |   1 |   0 |
|        3 |   1 |   0 |   0 |   0 |
|        4 |   2 |   0 |   1 |   1 |

But I am sure it can be done better. I would like to remove where I populate the variable @ColsP - SELECT @ColsP = STUFF((select...")

There should be a way where I can create this dynamic code with just one loop through TestTable1. As you can see, I loop through it twice. Once to read what columns to create for the select statement, and once for the PIVOT table.

Here is the code that is generated by the Dynamic SQL:

SELECT idnumber, 
  ISNULL([108], 0) AS '108',  
  ISNULL([109], 0) AS '109',  
  ISNULL([110], 0) AS '110',  
  ISNULL([111], 0) AS '111' 
FROM 
(
   SELECT  idnumber, COUNT([DataTypeId]) AS Total, [DataTypeId] 
   FROM [TestTable2] 
   GROUP BY  idnumber, [DataTypeId] 
   HAVING [DataTypeId] <> ''
) p
PIVOT
(
  SUM(Total) FOR [DataTypeId] IN ([108], [109], [110], [111])
) AS pvt 
ORDER BY pvt.idnumber

Answer 1

Try replacing it with this.

SET NOCOUNT ON
IF OBJECT_ID('TestTable1') IS NOT NULL
DROP TABLE TestTable1
GO

CREATE TABLE TestTable1 ([idnumber] INT, [DataTypeId] INT)
GO

INSERT INTO TestTable1 VALUES 
(1, 108),(1, 108),(1, 108),(2, 108),(2, 108),
(3, 108),(1, 109),(1, 109),(1, 110),(2, 110),
(1, 111),(4, 108),(4, 108),(4, 110),(4, 111)

DECLARE
    @AllColumns NVARCHAR(MAX)

SELECT @AllColumns = ''

SELECT @AllColumns = @AllColumns +
  '[' + CAST(DataTypeId as NVARCHAR)+'],' 
FROM TestTable1
GROUP BY DataTypeId


SET @AllColumns = LEFT(@AllColumns,LEN(@AllColumns)-1)
PRINT @AllColumns

Answer 2

You can shorten your code considerably. First, you can just use count to aggregate the data in the PIVOT. There is no need for the inner count to aggregate the data or the HAVING clause. Finally, you only need to create the list of columns once. You could easily improve the code to:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(DataTypeId) 
                    from TestTable1
                    group by DataTypeId
                    order by DataTypeId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
      = N'SELECT idnumber, ' + @cols + N' 
          from 
          (
            select idnumber, DataTypeId
            from TestTable1
          ) x
          pivot 
          (
            count(DataTypeId)
            for DataTypeId in (' + @cols + N')
          ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. This gives the same result:

| IDNUMBER | 108 | 109 | 110 | 111 |
|----------|-----|-----|-----|-----|
|        1 |   3 |   2 |   1 |   1 |
|        2 |   2 |   0 |   1 |   0 |
|        3 |   1 |   0 |   0 |   0 |
|        4 |   2 |   0 |   1 |   1 |