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 :

   

    

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 :

   

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