SQL Server dynamic PIVOT query?











up vote
152
down vote

favorite
74












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?










share|improve this question
























  • What version of SQL Server please?
    – Aaron Bertrand
    May 1 '12 at 21:14










  • possible duplicate of Write advanced SQL Select
    – RichardTheKiwi
    May 3 '13 at 9:43










  • thanks for this simple and helping question
    – smoothumut
    Dec 23 '17 at 8:25















up vote
152
down vote

favorite
74












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?










share|improve this question
























  • What version of SQL Server please?
    – Aaron Bertrand
    May 1 '12 at 21:14










  • possible duplicate of Write advanced SQL Select
    – RichardTheKiwi
    May 3 '13 at 9:43










  • thanks for this simple and helping question
    – smoothumut
    Dec 23 '17 at 8:25













up vote
152
down vote

favorite
74









up vote
152
down vote

favorite
74






74





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?










share|improve this 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?







sql sql-server tsql pivot






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 1 at 11:20









Laxmi

2,7111629




2,7111629










asked May 1 '12 at 21:05









Sean Cunningham

85321331




85321331












  • What version of SQL Server please?
    – Aaron Bertrand
    May 1 '12 at 21:14










  • possible duplicate of Write advanced SQL Select
    – RichardTheKiwi
    May 3 '13 at 9:43










  • thanks for this simple and helping question
    – smoothumut
    Dec 23 '17 at 8:25


















  • What version of SQL Server please?
    – Aaron Bertrand
    May 1 '12 at 21:14










  • possible duplicate of Write advanced SQL Select
    – RichardTheKiwi
    May 3 '13 at 9:43










  • thanks for this simple and helping question
    – smoothumut
    Dec 23 '17 at 8:25
















What version of SQL Server please?
– Aaron Bertrand
May 1 '12 at 21:14




What version of SQL Server please?
– Aaron Bertrand
May 1 '12 at 21:14












possible duplicate of Write advanced SQL Select
– RichardTheKiwi
May 3 '13 at 9:43




possible duplicate of Write advanced SQL Select
– RichardTheKiwi
May 3 '13 at 9:43












thanks for this simple and helping question
– smoothumut
Dec 23 '17 at 8:25




thanks for this simple and helping question
– smoothumut
Dec 23 '17 at 8:25












6 Answers
6






active

oldest

votes

















up vote
197
down vote



accepted










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





share|improve this answer



















  • 2




    Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
    – smoothdeveloper
    Feb 20 '15 at 2:40










  • So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
    – The Red Pea
    Oct 2 '15 at 20:08










  • How would you sort the rows and columns on this?
    – Patrick Schomburg
    Nov 29 '16 at 21:37










  • @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
    – Taryn
    Nov 29 '16 at 21:40






  • 1




    Nevermind I was putting the order by in the wrong spot.
    – Patrick Schomburg
    Nov 29 '16 at 21:48


















up vote
14
down vote













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





share|improve this answer




























    up vote
    7
    down vote













    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+','''') <> ''''
    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.



    enter image description here






    share|improve this answer





















    • Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
      – Przemyslaw Remin
      Sep 28 '17 at 11:21










    • Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
      – SFrejofsky
      Sep 28 '17 at 19:36




















    up vote
    6
    down vote













    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






    share|improve this answer



















    • 10




      FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
      – Aaron Bertrand
      May 1 '12 at 21:13






    • 2




      @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
      – Kermit
      Apr 1 '14 at 16:17












    • @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
      – davids
      Apr 29 '14 at 18:04






    • 2




      I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
      – davids
      Apr 29 '14 at 18:06


















    up vote
    3
    down vote













    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);





    share|improve this answer




























      up vote
      2
      down vote













      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 :



      enter image description here






      share|improve this answer




















        protected by Community Feb 12 '14 at 16:44



        Thank you for your interest in this question.
        Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



        Would you like to answer one of these unanswered questions instead?














        6 Answers
        6






        active

        oldest

        votes








        6 Answers
        6






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes








        up vote
        197
        down vote



        accepted










        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





        share|improve this answer



















        • 2




          Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
          – smoothdeveloper
          Feb 20 '15 at 2:40










        • So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
          – The Red Pea
          Oct 2 '15 at 20:08










        • How would you sort the rows and columns on this?
          – Patrick Schomburg
          Nov 29 '16 at 21:37










        • @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
          – Taryn
          Nov 29 '16 at 21:40






        • 1




          Nevermind I was putting the order by in the wrong spot.
          – Patrick Schomburg
          Nov 29 '16 at 21:48















        up vote
        197
        down vote



        accepted










        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





        share|improve this answer



















        • 2




          Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
          – smoothdeveloper
          Feb 20 '15 at 2:40










        • So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
          – The Red Pea
          Oct 2 '15 at 20:08










        • How would you sort the rows and columns on this?
          – Patrick Schomburg
          Nov 29 '16 at 21:37










        • @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
          – Taryn
          Nov 29 '16 at 21:40






        • 1




          Nevermind I was putting the order by in the wrong spot.
          – Patrick Schomburg
          Nov 29 '16 at 21:48













        up vote
        197
        down vote



        accepted







        up vote
        197
        down vote



        accepted






        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





        share|improve this 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






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Aug 30 '16 at 17:51

























        answered May 1 '12 at 21:13









        Taryn

        187k45284348




        187k45284348








        • 2




          Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
          – smoothdeveloper
          Feb 20 '15 at 2:40










        • So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
          – The Red Pea
          Oct 2 '15 at 20:08










        • How would you sort the rows and columns on this?
          – Patrick Schomburg
          Nov 29 '16 at 21:37










        • @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
          – Taryn
          Nov 29 '16 at 21:40






        • 1




          Nevermind I was putting the order by in the wrong spot.
          – Patrick Schomburg
          Nov 29 '16 at 21:48














        • 2




          Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
          – smoothdeveloper
          Feb 20 '15 at 2:40










        • So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
          – The Red Pea
          Oct 2 '15 at 20:08










        • How would you sort the rows and columns on this?
          – Patrick Schomburg
          Nov 29 '16 at 21:37










        • @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
          – Taryn
          Nov 29 '16 at 21:40






        • 1




          Nevermind I was putting the order by in the wrong spot.
          – Patrick Schomburg
          Nov 29 '16 at 21:48








        2




        2




        Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
        – smoothdeveloper
        Feb 20 '15 at 2:40




        Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)
        – smoothdeveloper
        Feb 20 '15 at 2:40












        So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
        – The Red Pea
        Oct 2 '15 at 20:08




        So @cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate @cols in there? Even though we construct @cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?
        – The Red Pea
        Oct 2 '15 at 20:08












        How would you sort the rows and columns on this?
        – Patrick Schomburg
        Nov 29 '16 at 21:37




        How would you sort the rows and columns on this?
        – Patrick Schomburg
        Nov 29 '16 at 21:37












        @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
        – Taryn
        Nov 29 '16 at 21:40




        @PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.
        – Taryn
        Nov 29 '16 at 21:40




        1




        1




        Nevermind I was putting the order by in the wrong spot.
        – Patrick Schomburg
        Nov 29 '16 at 21:48




        Nevermind I was putting the order by in the wrong spot.
        – Patrick Schomburg
        Nov 29 '16 at 21:48












        up vote
        14
        down vote













        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





        share|improve this answer

























          up vote
          14
          down vote













          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





          share|improve this answer























            up vote
            14
            down vote










            up vote
            14
            down vote









            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





            share|improve this 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






            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Jul 21 '16 at 13:18









            mkdave99

            342312




            342312






















                up vote
                7
                down vote













                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+','''') <> ''''
                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.



                enter image description here






                share|improve this answer





















                • Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                  – Przemyslaw Remin
                  Sep 28 '17 at 11:21










                • Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                  – SFrejofsky
                  Sep 28 '17 at 19:36

















                up vote
                7
                down vote













                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+','''') <> ''''
                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.



                enter image description here






                share|improve this answer





















                • Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                  – Przemyslaw Remin
                  Sep 28 '17 at 11:21










                • Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                  – SFrejofsky
                  Sep 28 '17 at 19:36















                up vote
                7
                down vote










                up vote
                7
                down vote









                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+','''') <> ''''
                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.



                enter image description here






                share|improve this 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+','''') <> ''''
                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.



                enter image description here







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jul 12 '17 at 18:58









                SFrejofsky

                499314




                499314












                • Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                  – Przemyslaw Remin
                  Sep 28 '17 at 11:21










                • Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                  – SFrejofsky
                  Sep 28 '17 at 19:36




















                • Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                  – Przemyslaw Remin
                  Sep 28 '17 at 11:21










                • Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                  – SFrejofsky
                  Sep 28 '17 at 19:36


















                Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                – Przemyslaw Remin
                Sep 28 '17 at 11:21




                Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.
                – Przemyslaw Remin
                Sep 28 '17 at 11:21












                Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                – SFrejofsky
                Sep 28 '17 at 19:36






                Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.
                – SFrejofsky
                Sep 28 '17 at 19:36












                up vote
                6
                down vote













                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






                share|improve this answer



















                • 10




                  FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                  – Aaron Bertrand
                  May 1 '12 at 21:13






                • 2




                  @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                  – Kermit
                  Apr 1 '14 at 16:17












                • @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                  – davids
                  Apr 29 '14 at 18:04






                • 2




                  I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                  – davids
                  Apr 29 '14 at 18:06















                up vote
                6
                down vote













                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






                share|improve this answer



















                • 10




                  FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                  – Aaron Bertrand
                  May 1 '12 at 21:13






                • 2




                  @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                  – Kermit
                  Apr 1 '14 at 16:17












                • @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                  – davids
                  Apr 29 '14 at 18:04






                • 2




                  I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                  – davids
                  Apr 29 '14 at 18:06













                up vote
                6
                down vote










                up vote
                6
                down vote









                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






                share|improve this 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







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited May 23 '17 at 12:34









                Community

                11




                11










                answered May 1 '12 at 21:11









                davids

                3,05984171




                3,05984171








                • 10




                  FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                  – Aaron Bertrand
                  May 1 '12 at 21:13






                • 2




                  @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                  – Kermit
                  Apr 1 '14 at 16:17












                • @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                  – davids
                  Apr 29 '14 at 18:04






                • 2




                  I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                  – davids
                  Apr 29 '14 at 18:06














                • 10




                  FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                  – Aaron Bertrand
                  May 1 '12 at 21:13






                • 2




                  @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                  – Kermit
                  Apr 1 '14 at 16:17












                • @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                  – davids
                  Apr 29 '14 at 18:04






                • 2




                  I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                  – davids
                  Apr 29 '14 at 18:06








                10




                10




                FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                – Aaron Bertrand
                May 1 '12 at 21:13




                FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.
                – Aaron Bertrand
                May 1 '12 at 21:13




                2




                2




                @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                – Kermit
                Apr 1 '14 at 16:17






                @davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.
                – Kermit
                Apr 1 '14 at 16:17














                @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                – davids
                Apr 29 '14 at 18:04




                @Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.
                – davids
                Apr 29 '14 at 18:04




                2




                2




                I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                – davids
                Apr 29 '14 at 18:06




                I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.
                – davids
                Apr 29 '14 at 18:06










                up vote
                3
                down vote













                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);





                share|improve this answer

























                  up vote
                  3
                  down vote













                  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);





                  share|improve this answer























                    up vote
                    3
                    down vote










                    up vote
                    3
                    down vote









                    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);





                    share|improve this 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);






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Dec 21 '15 at 15:09









                    m0rg4n

                    896




                    896






















                        up vote
                        2
                        down vote













                        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 :



                        enter image description here






                        share|improve this answer

























                          up vote
                          2
                          down vote













                          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 :



                          enter image description here






                          share|improve this answer























                            up vote
                            2
                            down vote










                            up vote
                            2
                            down vote









                            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 :



                            enter image description here






                            share|improve this 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 :



                            enter image description here







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Feb 27 '17 at 15:29









                            Arockia Nirmal

                            547415




                            547415

















                                protected by Community Feb 12 '14 at 16:44



                                Thank you for your interest in this question.
                                Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                                Would you like to answer one of these unanswered questions instead?



                                這個網誌中的熱門文章

                                Xamarin.form Move up view when keyboard appear

                                Post-Redirect-Get with Spring WebFlux and Thymeleaf

                                Anylogic : not able to use stopDelay()