Seperate month columns to single month column

Transact-SQL
1

Hi,

I have a table called SALES.
Basically it is formatted with separate horizontal columns for each
month like this…

Name Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Smith 2010 $100 $200 $300 $400 $500 $600 $700 $800 $900 $1,000 $1,100 $1,200
Jones 2010 $101 $201 $301 $401 $501 $601 $701 $801 $901 $1,001 $1,101 $1,201
Smith 2011 $1,000 $2,000 $3,000 $4,000 $5,000 $6,000 $7,000 $8,000 $9,000 $10,000 $11,000 $12,000
Jones 2011 $1,001 $2,001 $3,001 $4,001 $5,001 $6,001 $7,001 $8,001 $9,001 $10,001 $11,001 $12,001

I would like to rearrange it vertically. What would be the most efficient way to turn it into something like this…

Name Year Month Sales
Smith 2010 1 $100
Smith 2010 2 $200
…
Smith 2011 11 $11,000
Smith 2011 12 $12,000
Jones 2010 1 $101
Jones 2010 2 $201
…
Jones 2011 11 $11,001
Jones 2011 12 $12,002

Hopefully you get the idea....

I know I could do it with 12 separate monthly queries and UNION them together, but that doesn’t seem all that efficient. I am hoping there is a much better way.

Does anyone have an idea to help me out?

Thanks,

Gkaz

2

You can use UNPIVOT. If you post code that someone can copy to an SSMS window and run, it would be easier to respond. In the absence of that, I created a short example, see below. It can be adapted to your actual columns and tables.

If you are on an older version of SQL Server it may not support UNPIVOT. I think UNPIVOT was included in SQL 2005 (but I could be mistaken). In that case, you can simulate unpivot using aggregate functions.

CREATE TABLE #tmp(NAME VARCHAR(32), Yr INT, Jan INT, Feb INT, March INT);

INSERT INTO #tmp VALUES 
	('A', 2010, 10, 7, 11),
	('B', 2010, 17, 8, 22),
	('A', 2011, 155, 22, 33),
	('B', 2011, 1,0, 8);
	
	
SELECT
	*
FROM
	#tmp 
UNPIVOT 
( Sales FOR [Month] IN (Jan, Feb, March)) U
1 Like
3

Thanks JamesK

I think UNPIVOT will do the trick for me! Didn't even know that function was there.