Share via

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,601 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K 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

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Cooper 8,466 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
  2. Yitzhak Khabinsky 25,731 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
  3. MelissaMa-MSFT 24,191 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
  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