SQLTeam.com | Weblogs | Forums

Rows to columns and columns to rows without pivot

tsql
sql2012
sql2008

#1

All I need is convert Column(A1/A2) into rows and rows(1 into Jan) into columns.
Input:

Here A1/A2 belongs to say A and they are calculated as A1/A2 for each month.

Month A1 A2 B1 B2 C1 C2
1 120 60 40 80 120 120
2 50 50 40 20 60 30
3 50 25 40 10 90 30

I need below o/p without using pivot and unpivot

O/P:

X Jan(1 is denoting Jan) Feb Mar
A 120/60(calculation:A1/A2) 40/80 120/120
B 50/50 40/20 60/30
C 50/25 40/10 90/30

I tried but my query is too long as I am using case and Union All three times each For A1 A2,B1 B2,C1 C2 etc.

Can you pls help me out.
Thanks in advance


#2

Is this what you're looking for?

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	[Month] INT,
	 A1 INT,
	 A2 INT,
	 B1 INT,
	 B2 INT,
	 C1 INT,
	 C2 INT
	);

INSERT #TestData (Month, A1, A2, B1, B2, C1, C2) VALUES 
	(1, 120, 60, 40, 80, 120, 120),
	(2, 50, 50, 40, 20, 60, 30),
	(3, 50, 25, 40, 10, 90, 30);

SELECT * FROM #TestData td;



SELECT 
	x.C_Type,
	[Jan] = MAX(CASE WHEN td.[Month] = 1 THEN x.Calculation END),
	[Feb] = MAX(CASE WHEN td.[Month] = 2 THEN x.Calculation END),
	[Mar] = MAX(CASE WHEN td.[Month] = 3 THEN x.Calculation END),
	[Apr] = MAX(CASE WHEN td.[Month] = 4 THEN x.Calculation END),
	[May] = MAX(CASE WHEN td.[Month] = 5 THEN x.Calculation END),
	[Jun] = MAX(CASE WHEN td.[Month] = 6 THEN x.Calculation END),
	[Jul] = MAX(CASE WHEN td.[Month] = 7 THEN x.Calculation END),
	[Aug] = MAX(CASE WHEN td.[Month] = 8 THEN x.Calculation END),
	[Sep] = MAX(CASE WHEN td.[Month] = 9 THEN x.Calculation END),
	[Oct] = MAX(CASE WHEN td.[Month] = 10 THEN x.Calculation END),
	[Nov] = MAX(CASE WHEN td.[Month] = 11 THEN x.Calculation END),
	[Dec] = MAX(CASE WHEN td.[Month] = 12 THEN x.Calculation END)
FROM
	#TestData td
	CROSS APPLY ( VALUES
						('A', CAST(td.A1 AS VARCHAR(10)) + '/' + CAST(td.A2 AS VARCHAR(10))),
						('B', CAST(td.B1 AS VARCHAR(10)) + '/' + CAST(td.B2 AS VARCHAR(10))),
						('C', CAST(td.C1 AS VARCHAR(10)) + '/' + CAST(td.C2 AS VARCHAR(10)))
					) x (C_Type, Calculation)
GROUP BY 
	x.C_Type;