SQLTeam.com | Weblogs | Forums

Rows to columns and columns to rows without pivot

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

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;