Sample SQL Server code using pivot table

Question

I'm using a dynamic pivot example. This SQL code has the purpose of perform transposing data from rows to columns, using a temporary table in SQL Server.

The data is initially presented this way:

/*
cLinID cLinDescr    cColID  cColDescr    vValor
------ ------------ ------- ------------ -------------
1      Client 01    10      Product A    110.00000000
1      Client 01    20      Product B    120.00000000
1      Client 01    30      Product C    130.00000000
2      Client 02    40      Product A    210.00000000
2      Client 02    50      Product B    220.00000000
2      Client 02    60      Product C    230.00000000
*/

At the end of the process, after transform the column cColDescr into a row, the result is shown:

/*
cLinId cLinDescr Product A    Product B    Product C   
------ --------- ------------ ------------ ------------
1      Client 01 110.00000000 120.00000000 130.00000000
2      Client 02 210.00000000 220.00000000 230.00000000
*/

Drop the temp table:

if object_id('tempdb..#pvt_temp') is not null drop table #pvt_temp

Create temp table:

create table #pvt_temp
(
    cLinID     int,
    cLinDescr  nvarchar(MAX),
    cColID     int,
    cColDescr  nvarchar(MAX),
    vValor     DECIMAL(20,8)
)

Data to temporary Pivot Table:

insert into #pvt_temp
values
(1, 'Client 01', 10, 'Product A', 110.0),
(1, 'Client 01', 20, 'Product B', 120.0),
(1, 'Client 01', 30, 'Product C', 130.0),
(2, 'Client 02', 40, 'Product A', 210.0),
(2, 'Client 02', 50, 'Product B', 220.0),
(2, 'Client 02', 60, 'Product C', 230.0)

Dynamic Pivot Table:

DECLARE 
        @ColsSelect  AS NVARCHAR(MAX),
        @ColsFor     AS NVARCHAR(MAX),
        @query       AS NVARCHAR(MAX)

SET     @ColsFor = 
            STUFF(
                    (
                        SELECT   ',' + QUOTENAME(cColDescr)
                        from     #pvt_temp
                        group by cColDescr
                        order by cColDescr
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

SET     @ColsSelect = 
            STUFF(
                    (
                        SELECT   ', coalesce(' + QUOTENAME(cColDescr) + ', '''') as ' + QUOTENAME(''+ cColDescr )
                        from     #pvt_temp
                        group by cColDescr
                        order by cColDescr
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

SET   @query = 
      N'SELECT 
            cLinId,  
            cLinDescr, {@ColsSelect}
        from          
        (              
            select cLinId, cLinDescr, cColDescr, vValor 
            from #pvt_temp
        ) x          
        pivot          
        (   sum(vValor)              
            for cColDescr in 
            ({@ColsFor})          
        ) p'

SET @query = replace(@query,'{@ColsSelect}',@ColsSelect) 
SET @query = replace(@query,'{@ColsFor}'   ,@ColsFor)

Data initially presented:

select * from #pvt_temp

The final result:

execute(@query)

Answer 1

First of all be consistent:

• Use one variable convention (if @ColsFor => @Query).
• If you capitalize SQL keywords, do it everywhere.
• Use semicolons after each statement (yes, it is not mandatory but it is a good practice; MERGE/WITH require semicolons).
• DECLARE @var DATATYPE AS is not mandatory; you could skip it if you want.
• Specify column list when you use INSERT.
• You could be more concise (not so many newlines).
• If you are using SQL Server 2012+, consider using FORMATMESSAGE instead of multiple +.
• Do not use COALESCE(.., '') because '' cannot be implicitly casted to NUMERIC (demo) or use COALESCE(...,0).
• Use EXEC dbo.sp_executesql insead of EXECUTE (especially when you need to parameterize your query).

To sum up your code after review:

IF OBJECT_ID('tempdb..#pvt_temp') IS NOT NULL DROP TABLE #pvt_temp;

CREATE TABLE #pvt_temp(
    cLinID     INT,
    cLinDescr  NVARCHAR(MAX),
    cColID     INT,
    cColDescr  NVARCHAR(MAX),
    vValor     DECIMAL(20,8)
);

INSERT INTO #pvt_temp(cLinID, cLinDescr, cColID, cColDescr, vValor)
VALUES
(1, 'Client 01', 10, 'Product A', 110.0),
(1, 'Client 01', 20, 'Product B', 120.0),
(1, 'Client 01', 30, 'Product C', 130.0),
(2, 'Client 02', 40, 'Product A', 210.0),
(2, 'Client 02', 50, 'Product B', 220.0),
(2, 'Client 02', 60, 'Product C', 230.0);

DECLARE @ColsSelect  AS NVARCHAR(MAX),
        @ColsFor     AS NVARCHAR(MAX),
        @Query       AS NVARCHAR(MAX);
        
SET @ColsFor = STUFF((SELECT ',' + QUOTENAME(cColDescr)
                      FROM #pvt_temp
                      GROUP BY cColDescr
                      ORDER BY cColDescr
                      FOR XML PATH(''), TYPE).VALUE('.', 'NVARCHAR(MAX)')
                     ,1,1,'');      
                     

SET @ColsSelect = STUFF((SELECT FORMATMESSAGE(',COALESCE(%s, 0) AS %s',
                                              QUOTENAME(cColDescr),
                                              QUOTENAME(cColDescr))
                         FROM #pvt_temp
                         GROUP BY cColDescr
                         ORDER BY cColDescr
                         FOR XML PATH(''), TYPE).VALUE('.', 'NVARCHAR(MAX)')
                  ,1,1,'');
                  
SET @Query = 
       N'SELECT cLinId, cLinDescr, {@ColsSelect}
        FROM 
        (
          SELECT cLinId, cLinDescr, cColDescr, vValor 
          FROM #pvt_temp
        ) x               
        PIVOT    
        (   
          SUM(vValor) FOR cColDescr IN({@ColsFor})          
        ) p;'; 
        
SET @Query = REPLACE(@Query, '{@ColsSelect}', @ColsSelect);
SET @Query = REPLACE(@Query, '{@ColsFor}'   , @ColsFor);       
   
EXEC [dbo].[sp_executesql] @Query; 

LiveDemo

Output:

╔════════╦═══════════╦═══════════╦═══════════╦═══════════╗
║ cLinId ║ cLinDescr ║ Product A ║ Product B ║ Product C ║
╠════════╬═══════════╬═══════════╬═══════════╬═══════════╣
║      1 ║ Client 01 ║       110 ║       120 ║       130 ║
║      2 ║ Client 02 ║       210 ║       220 ║       230 ║
╚════════╩═══════════╩═══════════╩═══════════╩═══════════╝

---

If you cease using COALESCE(...,'') you could simplify it to:

DECLARE @ColsFor NVARCHAR(MAX),
        @Query   NVARCHAR(MAX);
        
SET @ColsFor = STUFF((SELECT ',' + QUOTENAME(cColDescr)
                      FROM #pvt_temp
                      GROUP BY cColDescr
                      ORDER BY cColDescr
                      FOR XML PATH(''), TYPE).VALUE('.', 'NVARCHAR(MAX)')
                     ,1,1,'');      
                    
SET @Query = 
       N'SELECT cLinId, cLinDescr, {@ColsFor}
        FROM 
        (
          SELECT cLinId, cLinDescr, cColDescr, vValor 
          FROM #pvt_temp
        ) x               
        PIVOT    
        (   
          SUM(vValor) FOR cColDescr IN({@ColsFor})          
        ) p;'; 
        
SET @Query = REPLACE(@Query, '{@ColsFor}', @ColsFor);       

EXEC [dbo].[sp_executesql] @Query;

LiveDemo

╔════════╦═══════════╦═══════════╦═══════════╦═══════════╗
║ cLinId ║ cLinDescr ║ Product A ║ Product B ║ Product C ║
╠════════╬═══════════╬═══════════╬═══════════╬═══════════╣
║      1 ║ Client 01 ║       110 ║       120 ║       130 ║
║      2 ║ Client 02 ║           ║       220 ║       230 ║
╚════════╩═══════════╩═══════════╩═══════════╩═══════════╝

Missing values should be indicated by NULL.