SQL Pivot columns to rows

Zimiso 121 Reputation points
2021-05-03T13:07:03.147+00:00

Good day,

I have the below table.

CREATE TABLE [dbo].ColSales(
[College Code] [float] NULL,
[Name] nvarchar NULL,
[Jan 2020] [float] NULL,
[Feb 2020] [float] NULL,
[Mar 2020] [float] NULL,
[Apr 2020] [float] NULL,
[May 2020] [float] NULL,
[Jun 2020] [float] NULL,
[Jul 2020] [float] NULL,
[Aug 2020] [float] NULL,
[Sep 2020] [float] NULL,
[Oct 2020] [float] NULL,
[Nov 2020] [float] NULL,
[Dec 2020] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ColSales] ([College Code], [Name], [Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020]) VALUES (3, N'Specist', 15400, 16540, 17680, 18820, 19960, 21100, 22240, 33500, 24520, 25660, 26800, 27940)
INSERT [dbo].[ColSales] ([College Code], [Name], [Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020]) VALUES (4, N'Foundation', 65000, 19642, 25716, 31790, 37864, 43938, 50012, 56086, 62160, 68234, 74308, 80382)
INSERT [dbo].[ColSales] ([College Code], [Name], [Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020]) VALUES (8, N'Tech', 8600, 18520, 28440, 38360, 48280, 58200, 68120, 78040, 87960, 97880, 107800, 117720)
INSERT [dbo].[ColSales] ([College Code], [Name], [Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020]) VALUES (9, N'Duplix', 5420, 7800, 10180, 12560, 14940, 17320, 19700, 22080, 24460, 26840, 29220, 31600)
GO

Please assist with pivoting, so I have 3 columns - College Code, Name, Period

Kindly advise,

Many thanks,

Regards

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,811 Reputation points
    2021-05-03T21:33:59.47+00:00

    Here is a solution that does not use the UNPIVOT keyword. It is a little more long-winded, but I think this is a more general way to do it.

    SELECT t.[College Code], t.Name, c.[Order Date], c.value
     FROM   @tbl t
     CROSS  APPLY (VALUES('Jan 2020', t.[Jan 2020]),
                          ('Feb 2020', t.[Feb 2020]),
                          ('Mar 2020', t.[Mar 2020]),
                          ('Apr 2020', t.[Apr 2020]),
                          ('May 2020', t.[May 2020]),
                          ('Jun 2020', t.[Jun 2020]),
                          ('Jul 2020', t.[Jul 2020]),
                          ('Aug 2020', t.[Aug 2020]),
                          ('Sep 2020', t.[Sep 2020]),
                          ('Oct 2020', t.[Oct 2020]),
                          ('Nov 2020', t.[Nov 2020]),
                          ('Dec 2020', t.[Dec 2020])) AS c([Order Date], value)
    
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,461 Reputation points
    2021-05-03T14:15:03.85+00:00

    Thanks for the sample table and data, that's very helpful. For this type of question it would also be helpful to show the output you would want given your sample data. So I'm not sure exactly what you are looking for.

    You say you want three columns, normally for this type of question, you would want 4 columns. I will give a query which has 4 columns. Hopefully that will get you started. If it doesn't help, please show us your desired result.

    SELECT [College Code], Name, Period, Amount  
    FROM ColSales  
    UNPIVOT  
       (Amount FOR Period IN   
          ([Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020])  
    )AS unpvt;
    

    Tom

    0 comments No comments

  2. Yitzhak Khabinsky 24,561 Reputation points
    2021-05-03T14:24:48.76+00:00

    Hi @Zimiso ,

    What you need is called unpivot operation.
    using-pivot-and-unpivot

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (  
    [College Code] [float] NULL,  
    [Name] [nvarchar](255) NULL,  
    [Jan 2020] [float] NULL,  
    [Feb 2020] [float] NULL,  
    [Mar 2020] [float] NULL,  
    [Apr 2020] [float] NULL,  
    [May 2020] [float] NULL,  
    [Jun 2020] [float] NULL,  
    [Jul 2020] [float] NULL,  
    [Aug 2020] [float] NULL,  
    [Sep 2020] [float] NULL,  
    [Oct 2020] [float] NULL,  
    [Nov 2020] [float] NULL,  
    [Dec 2020] [float] NULL  
    );   
    INSERT @tbl ([College Code], [Name], [Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020]) VALUES   
    (3, N'Specist', 15400, 16540, 17680, 18820, 19960, 21100, 22240, 33500, 24520, 25660, 26800, 27940),  
    (4, N'Foundation', 65000, 19642, 25716, 31790, 37864, 43938, 50012, 56086, 62160, 68234, 74308, 80382),  
    (8, N'Tech', 8600, 18520, 28440, 38360, 48280, 58200, 68120, 78040, 87960, 97880, 107800, 117720),  
    (9, N'Duplix', 5420, 7800, 10180, 12560, 14940, 17320, 19700, 22080, 24460, 26840, 29220, 31600);  
    -- DDL and sample data population, end  
      
    SELECT [College Code]  
          , [Name]  
       , [Order Date]   
          , [Value]  
    FROM @tbl  
    UNPIVOT (  
              [Value] FOR [Order Date]   
              IN ([Jan 2020], [Feb 2020], [Mar 2020], [Apr 2020], [May 2020], [Jun 2020], [Jul 2020], [Aug 2020], [Sep 2020], [Oct 2020], [Nov 2020], [Dec 2020])   
     ) AS [UNPIVOT TABLE];  
    
    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-05-04T01:54:36.12+00:00

    Hi @Zimiso

    Please also refer below dynamic way if you would not like to list all the column names.

    DECLARE @SQL NVARCHAR(MAX)  
      
    SELECT @SQL=STUFF((  
            SELECT ',' +QUOTENAME(COLUMN_NAME)  
    		FROM INFORMATION_SCHEMA.COLUMNS   
    		WHERE TABLE_NAME = 'ColSales' AND COLUMN_NAME NOT IN ('College Code','Name')  
    		FOR XML PATH('')  
                ), 1, 1, '')  
      
    SET @SQL= N'SELECT [College Code], Name, Period, Amount    
     FROM ColSales    
     UNPIVOT    
        (Amount FOR Period IN     
           ('+@SQL+ '))AS unpvt;'  
      
    EXECUTE sp_executesql @SQL  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Vladimir Moldovanenko 26 Reputation points
    2021-05-04T12:08:41.527+00:00

    Here is another fairly dynamic way. If you remove spaces from column names then xml will not have to escape spaces and query can be simplified

    SELECT
    x.[College Code]
    ,x.Name
    ,Attribute = REPLACE(c.Attribute, 'x0020', ' ')
    ,TRY_CAST(c.AttributeValue as float) as AttributeValue
    FROM
    (
    SELECT
    [College Code]
    ,[Name]
    ,x = (SELECT cs.* FOR XML RAW, TYPE)
    FROM [dbo].ColSales cs
    ) x
    CROSS APPLY x.x.nodes('row/@*') AS t(fp)
    CROSS APPLY(SELECT
    t.fp.value('local-name(.)', 'nvarchar(128)') as Attribute
    ,t.fp.value('.', 'nvarchar(128)') as AttributeValue
    ) c
    WHERE c.Attribute NOT IN ('Name', 'College_x0020_Code')

    0 comments No comments