March 21, 2012 at 8:26 pm
I need some help with PIVOT please. I just can't wrap my head around this one...
CREATE TABLE #Test
(
Company varchar(15),
City varchar(15),
Calc1 decimal(4,2),
Calc2 decimal(4,2),
Calc3 decimal(4,2),
Tier varchar(15),
TierStart int
)
INSERT INTO #Test
SELECT 'ABC','Chicago',2.1,4.0,5.2,'1-5',1 UNION
SELECT 'ABC','Chicago',4.1,4.0,7.2,'6-10',6 UNION
SELECT 'ABC','Chicago',2.5,4.6,5.2,'11-15',11 UNION
SELECT 'ABC','Chicago',2.2,4.1,3.2,'16-20',16 UNION
SELECT 'DEF','Paris',2.1,3.0,5.1,'1-5',1 UNION
SELECT 'DEF','Paris',4.1,4.0,6.2,'6-10',6 UNION
SELECT 'DEF','Paris',2.0,5.6,3.2,'11-15',11 UNION
SELECT 'DEF','Paris',2.2,4.1,3.2,'16-20',16
SELECT * FROM #Test ORDER BY Company,TierStart
DROP TABLE #Test
Desired results:
CompanyCityCalc1-56-1011-1516-20
ABCChicagoCalc12.104.102.502.20
ABCChicagoCalc24.004.004.604.10
ABCChicagoCalc35.207.205.203.20
DEFParisCalc12.104.102.002.20
DEFParisCalc23.004.005.604.10
DEFParisCalc35.106.203.203.20
March 21, 2012 at 8:49 pm
Let's start by having you read the two articles I reference below in the 5th line of my signature block regarding Cross Tabs and Pivots.
March 22, 2012 at 9:23 am
This seems to work. Using UNION for each calc seems somewhat weak on the programming elegance scale...
I'd like to stick with the cross tab approach rather than using PIVOT.
CREATE TABLE #Test
(
Company varchar(15),
City varchar(15),
Calc1 decimal(4,2),
Calc2 decimal(4,2),
Calc3 decimal(4,2),
Tier varchar(15),
TierStart int
)
INSERT INTO #Test
SELECT 'ABC','Chicago',2.1,4.0,5.2,'1-5',1 UNION
SELECT 'ABC','Chicago',4.1,4.0,7.2,'6-10',6 UNION
SELECT 'ABC','Chicago',2.5,4.6,5.2,'11-15',11 UNION
SELECT 'ABC','Chicago',2.2,4.1,3.2,'16-20',16 UNION
SELECT 'DEF','Paris',2.1,3.0,5.1,'1-5',1 UNION
SELECT 'DEF','Paris',4.1,4.0,6.2,'6-10',6 UNION
SELECT 'DEF','Paris',2.0,5.6,3.2,'11-15',11 UNION
SELECT 'DEF','Paris',2.2,4.1,3.2,'16-20',16
SELECT
Company,
City,
'Calc1' AS Calc,
MAX (CASE WHEN Tier = '1-5' THEN Calc1 ELSE 0 END)AS [1-5],
MAX (CASE WHEN Tier = '6-10' THEN Calc1 ELSE 0 END)AS [6-10],
MAX (CASE WHEN Tier = '11-15' THEN Calc1 ELSE 0 END)AS [11-15],
MAX (CASE WHEN Tier = '16-20' THEN Calc1 ELSE 0 END)AS [16-20]
FROM #Test
GROUP BY
Company,
City
UNION
SELECT
Company,
City,
'Calc2' AS Calc,
MAX (CASE WHEN Tier = '1-5' THEN Calc2 ELSE 0 END)AS [1-5],
MAX (CASE WHEN Tier = '6-10' THEN Calc2 ELSE 0 END)AS [6-10],
MAX (CASE WHEN Tier = '11-15' THEN Calc2 ELSE 0 END)AS [11-15],
MAX (CASE WHEN Tier = '16-20' THEN Calc2 ELSE 0 END)AS [16-20]
FROM #Test
GROUP BY
Company,
City
UNION
SELECT
Company,
City,
'Calc3' AS Calc,
MAX (CASE WHEN Tier = '1-5' THEN Calc3 ELSE 0 END)AS [1-5],
MAX (CASE WHEN Tier = '6-10' THEN Calc3 ELSE 0 END)AS [6-10],
MAX (CASE WHEN Tier = '11-15' THEN Calc3 ELSE 0 END)AS [11-15],
MAX (CASE WHEN Tier = '16-20' THEN Calc3 ELSE 0 END)AS [16-20]
FROM #Test
GROUP BY
Company,
City
SELECT * FROM #Test ORDER BY Company,TierStart
DROP TABLE #Test
March 22, 2012 at 9:46 am
i dont think 4 UNIONs are not required.. Try this:
; WITH CTE AS
(
SELECT Company , City, Vals , Tier , TierVals
FROM #Test T
UNPIVOT ( TierVals For Vals in (Calc1, Calc2,Calc3)) PivotHandle
)
SELECT C.Company , C.City , C.Vals
, [Tier 1-5] = MAX( CASE WHEN C.Tier = '1-5' THEN C.TierVals ELSE 0 END )
, [Tier 6-10] = MAX( CASE WHEN C.Tier = '6-10' THEN C.TierVals ELSE 0 END )
, [Tier 11-15] = MAX( CASE WHEN C.Tier = '11-15' THEN C.TierVals ELSE 0 END )
, [Tier 16-20] = MAX( CASE WHEN C.Tier = '16-20' THEN C.TierVals ELSE 0 END )
FROM CTE C
GROUP BY C.Company , C.City , C.Vals
{Edit : Wrong IFCode used}
March 22, 2012 at 10:12 am
Thanks again ColdCoffee. Works great. I think the fact that I had to cross-tab and UNPIVOT to get the desired results was confusing me. It seems my UNION technique is a way to 'unpivot' before the UNPIVOT function existed.
March 22, 2012 at 10:20 am
Yeah.. and u'r welcome chrissy..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply