locked
How to turn a table 'upside down'? RRS feed

  • Question

  • Hello,

    I have a table like this:

    I want to 'turn it around' ( I am sure there is some fancy expression for this ) so that the result looks like this:

    Is this possible?

    Thank you.

    Wednesday, March 27, 2013 9:58 AM

Answers

  • That's a unpivot operation. This can be done with the UNPIVOT operator, which in difference to the PIVOT operator has some merit. But since I have not learnt that syntax yet, I use a more ANSI-compatible version:

    SELECT Market, District, Type, n.n
           CASE n.n WHEN 1 THEN Jan
                    WHEN 2 THEN Feb
                    ...
                    WHEN 12 THEN Dec
           END
    FROM   tbl
    CROSS  JOIN (SELECT n = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
                 SELECT n = 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
                 SELECT n = 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
                 SELECT n = 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS n

    Instead of the SELECT UNION ALL you could use a Numbers table if you have one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SPEHE Tuesday, June 2, 2015 8:26 AM
    Wednesday, March 27, 2013 10:05 AM
  • Declare @Table Table
    (
    Market Int,
    District Varchar(10),
    Types Varchar(10),
    Jan Varchar(10),
    Feb Varchar(10),
    Mar Varchar(10),
    Apr Varchar(10),
    May Varchar(10),
    Jun Varchar(10),
    Jul Varchar(10),
    Aug Varchar(10),
    Sep Varchar(10),
    Oct Varchar(10),
    Nov Varchar(10),
    Decm Varchar(10)
    )

    Insert @Table
    Select 1 ,'S210','Type1',397837,24536,6578,767,879,767,878,35443,980,87,7656,76 Union All
    Select 1 ,'S210','Type2',397837,24536,4555,767,879,767,4354,765,354345,87,7656,769 Union All
    Select 1 ,'S210','Type3',397837,24536,6578,345345,43543,454,878,765,980,87,7656,76;



    Select Market,District,Types,Row_Number()Over(Partition By Types Order By Types) Period,Value As Budg From @Table
    UnPivot (Value For Mak In ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[decm]))Upv

    Please have look on the comment

    • Marked as answer by SPEHE Wednesday, March 27, 2013 10:35 AM
    Wednesday, March 27, 2013 10:15 AM

All replies

  • Have a look at the SQL function UNPIVOT. Thanks,

    Mark

    Wednesday, March 27, 2013 10:02 AM
  • I have tried to figure UNPIVOT out but I do not understand how to use it in this case.
    Wednesday, March 27, 2013 10:04 AM
  • That's a unpivot operation. This can be done with the UNPIVOT operator, which in difference to the PIVOT operator has some merit. But since I have not learnt that syntax yet, I use a more ANSI-compatible version:

    SELECT Market, District, Type, n.n
           CASE n.n WHEN 1 THEN Jan
                    WHEN 2 THEN Feb
                    ...
                    WHEN 12 THEN Dec
           END
    FROM   tbl
    CROSS  JOIN (SELECT n = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
                 SELECT n = 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
                 SELECT n = 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
                 SELECT n = 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS n

    Instead of the SELECT UNION ALL you could use a Numbers table if you have one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SPEHE Tuesday, June 2, 2015 8:26 AM
    Wednesday, March 27, 2013 10:05 AM
  • Declare @Table Table
    (
    Market Int,
    District Varchar(10),
    Types Varchar(10),
    Jan Varchar(10),
    Feb Varchar(10),
    Mar Varchar(10),
    Apr Varchar(10),
    May Varchar(10),
    Jun Varchar(10),
    Jul Varchar(10),
    Aug Varchar(10),
    Sep Varchar(10),
    Oct Varchar(10),
    Nov Varchar(10),
    Decm Varchar(10)
    )

    Insert @Table
    Select 1 ,'S210','Type1',397837,24536,6578,767,879,767,878,35443,980,87,7656,76 Union All
    Select 1 ,'S210','Type2',397837,24536,4555,767,879,767,4354,765,354345,87,7656,769 Union All
    Select 1 ,'S210','Type3',397837,24536,6578,345345,43543,454,878,765,980,87,7656,76;



    Select Market,District,Types,Row_Number()Over(Partition By Types Order By Types) Period,Value As Budg From @Table
    UnPivot (Value For Mak In ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[decm]))Upv

    Please have look on the comment

    • Marked as answer by SPEHE Wednesday, March 27, 2013 10:35 AM
    Wednesday, March 27, 2013 10:15 AM
  • After some fiddling around, I got this to work. Thank you to everyone who helped out.
    Wednesday, March 27, 2013 10:36 AM
  • Select Market,District,Types,Row_Number()Over(Partition By Types Order By Types) Period,Value As Budg From @Table
    Cross Apply ( Values ([Jan]),([Feb]),([Mar]),([Apr]),([May]),([Jun]),([Jul]),([Aug]),([Sep]),([Oct]),([Nov]),([decm])) B(Value)

    • Proposed as answer by Naomi N Thursday, March 28, 2013 3:23 AM
    Wednesday, March 27, 2013 9:08 PM
  • Have a look at my solution.

    I created such a table: (I wish I could put a screenshot here but my account is not verified)

    CREATE TABLE [dbo].[Ex1](
    	[Market] [varchar](50) NULL,
    	[District] [varchar](50) NULL,
    	[Type] [varchar](50) NULL,
    	[January] [float] NULL,
    	[February] [float] NULL,
    	[March] [float] NULL,
    	[April] [float] NULL,
    	[May] [float] NULL,
    	[June] [float] NULL,
    	[July] [float] NULL,
    	[August] [float] NULL,
    	[September] [float] NULL,
    	[October] [float] NULL,
    	[November] [float] NULL,
    	[December] [float] NULL
    ) ON [PRIMARY]

    then I queried:

    SELECT
    	Market
    	,District
    	,Type
    	,DATEPART(MM, U.Period + '11 1111') AS Period
    	,U.Budget
    FROM Ex1
    UNPIVOT(
    	Budget for Period IN (
    		[January]
    		,[February]
    		,[March]
    		,[April]
    		,[May]
    		,[June]
    		,[July]
    		,[August]
    		,[September]
    		,[October]
    		,[November]
    		,[December])
    	) AS U

    and I got:

    Market District Type Period Budget 1 S210 TYPE1 1 0,28 1 S210 TYPE1 2 0,48 1 S210 TYPE1 3 0,48 1 S210 TYPE1 4 0,61 1 S210 TYPE1 5 0,49 1 S210 TYPE1 6 0,59 1 S210 TYPE1 7 0,09 1 S210 TYPE1 8 0,04 1 S210 TYPE1 9 0,13 1 S210 TYPE1 10 0,81 1 S210 TYPE1 11 0,1 1 S210 TYPE1 12 0,22 1 S210 TYPE2 1 0,62 1 S210 TYPE2 2 0,54 1 S210 TYPE2 3 0,3 1 S210 TYPE2 4 0,03 1 S210 TYPE2 5 0,45 1 S210 TYPE2 6 0,19 1 S210 TYPE2 7 0,55 1 S210 TYPE2 8 0,95 1 S210 TYPE2 9 0,52 1 S210 TYPE2 10 0,1 1 S210 TYPE2 11 0,97 1 S210 TYPE2 12 0,45 1 S210 TYPE3 1 0,12 1 S210 TYPE3 2 0,99 1 S210 TYPE3 3 0,6 1 S210 TYPE3 4 0,15 1 S210 TYPE3 5 0,69 1 S210 TYPE3 6 0,04 1 S210 TYPE3 7 0,54 1 S210 TYPE3 8 0,97 1 S210 TYPE3 9 0,55 1 S210 TYPE3 10 0,25 1 S210 TYPE3 11 0,01 1 S210 TYPE3 12 0,31


    I will appreciate if you write it's fine or not. :-)

    Many candies.


    • Edited by sweetsql Thursday, March 28, 2013 8:35 PM look
    • Proposed as answer by sweetsql Thursday, March 28, 2013 8:36 PM
    Thursday, March 28, 2013 8:34 PM