SQL Server dynamic PIVOT query

Question

I ve been tasked with coming up with a means of translating the following data   date        category        amount 1 1 2012    ABC             1000 00 2 1 2012    DEF             500 00 2 1 2012    GHI             800 00 2 10 2012   DEF             700 00 3 1 2012    ABC             1100 00   into the following   date        ABC             DEF             GHI 1 1 2012    1000 00 2 1 2012                    500 00 2 1 2012                                    800 00 2 10 2012                   700 00 3 1 2012    1100 00   The blank spots can be NULLs or blanks  either is fine  and the categories would need to be dynamic  Another possible caveat to this is that we ll be running the query in a limited capacity  which means temp tables are out  I ve tried to research and have landed on PIVOT but as I ve never used that before I really don t understand it  despite my best efforts to figure it out  Can anyone point me in the right direction

User · Answer

Dynamic SQL PIVOT

Different approach for creating columns string

create table #temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select date, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

Result

date                    ABC     DEF     GHI
2012-01-01 00:00:00.000 1000.00 NULL    NULL
2012-02-01 00:00:00.000 NULL    500.00  800.00
2012-02-10 00:00:00.000 NULL    700.00  NULL
2012-03-01 00:00:00.000 1100.00 NULL    NULL

User · Answer

The below code provides the results which replaces NULL to zero in the output   Table creation and data insertion   create table test table     date nvarchar 10    category char 3    amount money      insert into test table values   1 1 2012   ABC  1000 00   insert into test table values   2 1 2012   DEF  500 00   insert into test table values   2 1 2012   GHI  800 00   insert into test table values   2 10 2012   DEF  700 00   insert into test table values   3 1 2012   ABC  1100 00    Query to generate the exact results which also replaces NULL with zeros    DECLARE  DynamicPivotQuery AS NVARCHAR MAX    PivotColumnNames AS NVARCHAR MAX    PivotSelectColumnNames AS NVARCHAR MAX   --Get distinct values of the PIVOT Column SELECT  PivotColumnNames  ISNULL  PivotColumnNames             QUOTENAME category  FROM  SELECT DISTINCT category FROM test table  AS cat  --Get distinct values of the PIVOT Column with isnull SELECT  PivotSelectColumnNames    ISNULL  PivotSelectColumnNames              ISNULL     QUOTENAME category       0  AS     QUOTENAME category  FROM  SELECT DISTINCT category FROM test table  AS cat  --Prepare the PIVOT query using the dynamic  SET  DynamicPivotQuery    N SELECT date       PivotSelectColumnNames     FROM test table pivot sum amount  for category in       PivotColumnNames       as pvt    --Execute the Dynamic Pivot Query EXEC sp executesql  DynamicPivotQuery   OUTPUT

User · Answer

I know this question is older but I was looking thru the answers and thought that I might be able to expand on the  dynamic  portion of the problem and possibly help someone out   First and foremost I built this solution to solve a problem a couple of coworkers were having with inconstant and large data sets needing to be pivoted quickly   This solution requires the creation of a stored procedure so if that is out of the question for your needs please stop reading now    This procedure is going to take in the key variables of a pivot statement to dynamically create pivot statements for varying tables  column names and aggregates  The Static column is used as the group by   identity column for the pivot this can be stripped out of the code if not necessary but is pretty common in pivot statements and was necessary to solve the original issue   the pivot column is where the end resultant column names will be generated from  and the value column is what the aggregate will be applied to  The Table parameter is the name of the table including the schema  schema tablename  this portion of the code could use some love because it is not as clean as I would like it to be  It worked for me because my usage was not publicly facing and sql injection was not a concern  The Aggregate parameter will accept any standard sql aggregate  AVG    SUM    MAX  etc  The code also defaults to MAX as an aggregate this is not necessary but the audience this was originally built for did not understand pivots and were typically using max as an aggregate   Lets start with the code to create the stored procedure  This code should work in all versions of SSMS 2005 and above but I have not tested it in 2005 or 2016 but I can not see why it would not work   create PROCEDURE  dbo   USP DYNAMIC PIVOT                 STATIC COLUMN VARCHAR 255            PIVOT COLUMN VARCHAR 255            VALUE COLUMN VARCHAR 255            TABLE VARCHAR 255            AGGREGATE VARCHAR 20    null        AS   BEGIN  SET NOCOUNT ON  declare  AVAIABLE TO PIVOT NVARCHAR MAX            SQLSTRING NVARCHAR MAX            PIVOT SQL STRING NVARCHAR MAX            TEMPVARCOLUMNS NVARCHAR MAX            TABLESQL NVARCHAR MAX   if isnull  AGGREGATE               begin         SET  AGGREGATE    MAX      end    SET  PIVOT SQL STRING       SELECT top 1 STUFF  SELECT distinct          CAST       CONVERT VARCHAR     PIVOT COLUMN           AS VARCHAR 50    text                                FROM    TABLE                               WHERE ISNULL    PIVOT COLUMN          lt  gt                                   FOR XML PATH        TYPE                               value         NVARCHAR MAX     1 2        as PIVOT VALUES                             from    TABLE   ma                             ORDER BY      PIVOT COLUMN       declare  TAB AS TABLE COL NVARCHAR MAX     INSERT INTO  TAB EXEC SP EXECUTESQL   PIVOT SQL STRING   AVAIABLE TO PIVOT   SET  AVAIABLE TO PIVOT    SELECT   FROM  TAB    SET  TEMPVARCOLUMNS    SELECT replace  AVAIABLE TO PIVOT       nvarchar 255  null        nvarchar 255  null     SET  SQLSTRING    DECLARE  RETURN TABLE TABLE     STATIC COLUMN   NVARCHAR 255  NULL    TEMPVARCOLUMNS                          INSERT INTO  RETURN TABLE    STATIC COLUMN      AVAIABLE TO PIVOT                         select   from                       SELECT      STATIC COLUMN            PIVOT COLUMN           VALUE COLUMN     FROM    TABLE     a                      PIVOT                                              AGGREGATE      VALUE COLUMN                        FOR    PIVOT COLUMN   IN     AVAIABLE TO PIVOT                          piv                      SELECT   FROM  RETURN TABLE     EXEC SP EXECUTESQL  SQLSTRING  END   Next we will get our data ready for the example  I have taken the data example from the accepted answer with the addition of a couple of data elements to use in this proof of concept to show the varied outputs of the aggregate change   create table temp       date datetime      category varchar 3       amount money    insert into temp values   1 1 2012    ABC   1000 00  insert into temp values   1 1 2012    ABC   2000 00  -- added insert into temp values   2 1 2012    DEF   500 00  insert into temp values   2 1 2012    DEF   1500 00  -- added insert into temp values   2 1 2012    GHI   800 00  insert into temp values   2 10 2012    DEF   700 00  insert into temp values   2 10 2012    DEF   800 00  -- addded insert into temp values   3 1 2012    ABC   1100 00    The following examples show the varied execution statements showing the varied aggregates as a simple example  I did not opt to change the static  pivot  and value columns to keep the example simple  You should be able to just copy and paste the code to start messing with it yourself    exec  dbo   USP DYNAMIC PIVOT   date   category   amount   dbo temp   sum  exec  dbo   USP DYNAMIC PIVOT   date   category   amount   dbo temp   max  exec  dbo   USP DYNAMIC PIVOT   date   category   amount   dbo temp   avg  exec  dbo   USP DYNAMIC PIVOT   date   category   amount   dbo temp   min    This execution returns the following data sets respectively

User · Answer

Updated version for SQL Server 2017 using STRING AGG function to construct the pivot column list   create table temp       date datetime      category varchar 3       amount money     insert into temp values   20120101    ABC   1000 00   insert into temp values   20120201    DEF   500 00   insert into temp values   20120201    GHI   800 00   insert into temp values   20120210    DEF   700 00   insert into temp values   20120301    ABC   1100 00     DECLARE  cols AS NVARCHAR MAX        query  AS NVARCHAR MAX    SET  cols    SELECT STRING AGG category      FROM  SELECT DISTINCT category FROM temp WHERE category IS NOT NULL t    set  query    SELECT date       cols     from                                select date                       amount                       category                 from temp              x             pivot                                 max amount                  for category in       cols                    p     execute  query    drop table temp

User · Answer

Dynamic SQL PIVOT   create table temp       date datetime      category varchar 3       amount money    insert into temp values   1 1 2012    ABC   1000 00  insert into temp values   2 1 2012    DEF   500 00  insert into temp values   2 1 2012    GHI   800 00  insert into temp values   2 10 2012    DEF   700 00  insert into temp values   3 1 2012    ABC   1100 00    DECLARE  cols AS NVARCHAR MAX        query  AS NVARCHAR MAX    SET  cols   STUFF  SELECT distinct       QUOTENAME c category               FROM temp c             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      set  query    SELECT date       cols     from                                select date                       amount                       category                 from temp              x             pivot                                 max amount                  for category in       cols                    p     execute  query   drop table temp   Results   Date                        ABC         DEF    GHI 2012-01-01 00 00 00 000     1000 00     NULL    NULL 2012-02-01 00 00 00 000     NULL        500 00  800 00 2012-02-10 00 00 00 000     NULL        700 00  NULL 2012-03-01 00 00 00 000     1100 00     NULL    NULL

User · Answer

You can achieve this using dynamic TSQL  remember to use QUOTENAME to avoid SQL injection attacks    Pivots with Dynamic Columns in SQL Server 2005  SQL Server - Dynamic PIVOT Table - SQL Injection  Obligatory reference to The Curse and Blessings of Dynamic SQL

User · Answer

There s my solution cleaning up the unnecesary null values  DECLARE  cols AS NVARCHAR MAX    maxcols AS NVARCHAR MAX    query  AS NVARCHAR MAX   select  cols   STUFF  SELECT       QUOTENAME CodigoFormaPago                   from PO FormasPago                 order by CodigoFormaPago         FOR XML PATH      TYPE           value       NVARCHAR MAX          1 1      select  maxcols   STUFF  SELECT   MAX     QUOTENAME CodigoFormaPago       as     QUOTENAME CodigoFormaPago                  from PO FormasPago                 order by CodigoFormaPago         FOR XML PATH      TYPE           value       NVARCHAR MAX         1 1      set  query    SELECT CodigoProducto  DenominacionProducto       maxcols                 FROM                               SELECT                  CodigoProducto  DenominacionProducto                       cols     from                                         SELECT                          p CodigoProducto as CodigoProducto                          p DenominacionProducto as DenominacionProducto                          fpp CantidadCuotas as CantidadCuotas                          fpp IdFormaPago as IdFormaPago                          fp CodigoFormaPago as CodigoFormaPago                     FROM                         PR Producto p                         LEFT JOIN PR FormasPagoProducto fpp                             ON fpp IdProducto   p IdProducto                         LEFT JOIN PO FormasPago fp                             ON fpp IdFormaPago   fp IdFormaPago                   xp                 pivot                                        MAX CantidadCuotas                      for CodigoFormaPago in       cols                        p                 xx              GROUP BY CodigoProducto  DenominacionProducto   t  query   execute  query

[sql] SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to pivot