-2
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ I'm providing consulting services to a bank. In this place, the consultors cannot access external e-mails. And the only way I could find to send a sample code that I wrote at home, to access from this bank was here as a question from Stack Exchange Code Review. So initially, I haven't enough time to write a proper description to this code. I edited the question, \$\endgroup\$ – nandox Feb 5 '16 at 23:42
  • \$\begingroup\$ ... I edited the question, I hope that after the changes the posting has become easier to understand. \$\endgroup\$ – nandox Feb 5 '16 at 23:49
  • \$\begingroup\$ I think the 3 negative votes was because the first time I posted this sample, I not wrote the finality of the code. \$\endgroup\$ – nandox Feb 6 '16 at 19:06
1
\$\begingroup\$

First of all be consistent:

  • the same 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 conscice (not so many new line)
  • 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 parametrize 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 resign from 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.

\$\endgroup\$
  • \$\begingroup\$ ... I'm using SQL Server 2008 (there is no support for formatmessage). I appreciate your review. All your points are well taken. Sorry about the English mistakes. I'm not fluent. \$\endgroup\$ – nandox Feb 8 '16 at 4:31
  • \$\begingroup\$ @nandox You could accept my answer if your question has been solved :) \$\endgroup\$ – lad2025 Feb 10 '16 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.