How to turn a table 'upside down'?

All replies

• 

  

  1

  Sign in to vote

  Have a look at the SQL function UNPIVOT. Thanks,

  Mark

  Wednesday, March 27, 2013 10:02 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  After some fiddling around, I got this to work. Thank you to everyone who helped out.

  Wednesday, March 27, 2013 10:36 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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