Convert Rows to Columns using Pivot in SQL

In this article, we will overview the SQL queries for how to convert rows to columns using pivot in SQL.

Please review the following steps :

  1. Here I have created one table with relatable data :
    CREATE TABLE Product 
    (
    [StoreId] int, 
    [Month] int, 
    [Count] int
    );
    
    INSERT INTO Product
    (
    [StoreId], [Month], [Count]
    )
    VALUES
    (102, 1, 96), (101, 1, 138), (105, 1, 37), (109, 1, 59),
    (101, 2, 282), (102, 2, 212), (105, 2, 78), (109, 2, 97),
    (105, 3, 60), (102, 3, 123), (101, 3, 220), (109, 3, 87),
    (102, 4, 96), (101, 4, 138), (105, 4, 37), (109, 4, 59),
    (101, 5, 282), (102, 5, 212), (105, 5, 78), (109, 5, 97),
    (105, 6, 60), (102, 6, 123), (101, 6, 220), (109, 6, 87),
    (102, 7, 96), (101, 7, 138), (105, 7, 37), (109, 7, 59),
    (101, 8, 282), (102, 8, 212), (105, 8, 78), (109, 8, 97),
    (105, 9, 60), (102, 9, 123), (101, 9, 220), (109, 9, 87),
    (102, 10, 96), (101, 10, 138), (105, 10, 37), (109, 10, 59),
    (101, 11, 282), (102, 11, 212), (105, 11, 78), (109, 11, 97),
    (105, 12, 60), (102, 12, 123), (101, 12, 220), (109, 12, 87);

     

     

  2. If the Month data are predefined, then you will hard-code the select query like this:
    select *
    from 
    (
      select StoreId, Month, Count
      from Product 
    ) src
    pivot
    (
      sum(Count)
      for Month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) piv;

    Output :

    sql pivot

     

  3. If you want to generate the Month number dynamically, then you have to write and execute a select query like this:
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(Month) 
                        from Product
                        group by Month
                        order by Month
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT StoreId,' + @cols + ' from 
                 (
                    select StoreId, Month, Count
                    from Product
                ) x
                pivot 
                (
                    sum(Count)
                    for Month in (' + @cols + ')
                ) p '
    
    execute(@query);

    Output :

    sql pivot table

 

Hope this will help you with the pivot table concept.
if you have any questions or issues about this article, please let me know.

Submit a Comment

Your email address will not be published. Required fields are marked *

Footer Logo

Subscribe

Select Categories