Union or CTE to turn cols into rows?

Question

Union or CTE to turn cols into rows?

rjjh78

SSCrazy

Points: 2784
More actions
August 8, 2017 at 7:47 am

#332180

I am looking for the most efficient way to turn columns into rows where I do not believe I can use a pivot because there is not enough data. I currently am using a UNION but curious if CTE would be more efficient? And if CTE is better, can someone provide sample code?
My UNION sample code is as follows:
--===================================================================================================== -- 1. Source Table Containing Test Data --=====================================================================================================
-- DROP TABLE #SrcTbl
CREATE TABLE #SrcTbl (Loc int, Yr int, Qtr1 int, Qtr2 int, Qtr3 int, Qtr4 int)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (1,2017,$300.00,$250.00,$535.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (2,2017,$100.00,$95.00, $88.00, $0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (3,2017,$200.00,$250.00,$235.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (4,2017,$900.00,$1250.00,$1735.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (5,2017,$1300.00,$2000.00,$1135.00,$0.00)
-- SELECT * FROM #SrcTbl
--=====================================================================================================
-- 2. Conform data to present Qtr as a column instead of row
--=====================================================================================================
-- DROP TABLE #ColToRow
CREATE TABLE #ColToRow (Loc int, Yr int, Qtr int, Dol money)
INSERT INTO #ColToRow (Loc, Yr, Qtr, Dol)
SELECT Loc, Yr, '1' as Qtr, Qtr1 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '2' as Qtr, Qtr1 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '3' as Qtr, Qtr3 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '4' as Qtr, Qtr4 FROM #SrcTbl
-- SELECT * FROM #ColToRow
Thanks in advance!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

rjjh78 - Tuesday, August 8, 2017 7:47 AM
I am looking for the most efficient way to turn columns into rows where I do not believe I can use a pivot because there is not enough data. I currently am using a UNION but curious if CTE would be more efficient? And if CTE is better, can someone provide sample code?
My UNION sample code is as follows:
--===================================================================================================== -- 1. Source Table Containing Test Data --=====================================================================================================
-- DROP TABLE #SrcTbl
CREATE TABLE #SrcTbl (Loc int, Yr int, Qtr1 int, Qtr2 int, Qtr3 int, Qtr4 int)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (1,2017,$300.00,$250.00,$535.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (2,2017,$100.00,$95.00, $88.00, $0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (3,2017,$200.00,$250.00,$235.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (4,2017,$900.00,$1250.00,$1735.00,$0.00)
INSERT INTO #SrcTbl (Loc, Yr, Qtr1, Qtr2, Qtr3, Qtr4) VALUES (5,2017,$1300.00,$2000.00,$1135.00,$0.00)
-- SELECT * FROM #SrcTbl
--=====================================================================================================
-- 2. Conform data to present Qtr as a column instead of row
--=====================================================================================================
-- DROP TABLE #ColToRow
CREATE TABLE #ColToRow (Loc int, Yr int, Qtr int, Dol money)
INSERT INTO #ColToRow (Loc, Yr, Qtr, Dol)
SELECT Loc, Yr, '1' as Qtr, Qtr1 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '2' as Qtr, Qtr1 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '3' as Qtr, Qtr3 FROM #SrcTbl
UNION ALL
SELECT Loc, Yr, '4' as Qtr, Qtr4 FROM #SrcTbl
-- SELECT * FROM #ColToRow
Thanks in advance!

A CTE won't really help, but you could use the unpivot operator. Although, I prefer a different and more flexible method. The method is explained in here:
An Alternative (Better?) Method to UNPIVOT (SQL Spackle) - SQLServerCentral
And here's a sample:
SELECT t.Loc, t.Yr, up.Qtr, up.Dol FROM #SrcTbl t CROSS APPLY (VALUES ( 1, t.Qtr1), ( 2, t.Qtr2), ( 3, t.Qtr3), ( 4, t.Qtr4))up(Qtr, Dol);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rjjh78 SSCrazy Points: 2784 More actions · Answer 2

Thank you for the sample and sharing this article, I need to become an expert at these tools!