Pseudo PIVOT query
-
I have data in my base table as shown below.
I need to convert this data into the format below for reporting purposes. This is not quite pivot but somewhat closer.
For now I want to be able to do this with a query. Could anyone please suggest an efficient way to get this done?
I may have to load this data into my data warehouse in future, so if I were to do the same using SSIS what transformation should I be using?
Below is the SQL to generate sample data to a table variable.
DECLARE @MySampleTable TABLE (
MatchIDINT,
MatchDateDATETIME2,
VenueIDINT,
HostingTeamVARCHAR (100),
VisitingTeamVARCHAR (100),
HostFoulsINT,
VisitorFoulsINT,
HoastGoalsINT,
VisitorGoalsINT
)
INSERT @MySampleTable VALUES (101, '10/15/2013', 2007, 'Bashers', 'Rhinos', 12, 19, 3, 1)
INSERT @MySampleTable VALUES (102, '10/15/2013', 3041, 'Kickers', 'Pumas', 23, 26, 2, 4)
INSERT @MySampleTable VALUES (103, '10/16/2013', 1922, 'Boxers', 'Bashers', 14, 18, 0, 0)
SELECT * FROM @MySampleTable
Thanks in advance.
-
SELECT
MatchID, KeyCode,
CASE KeyCode
WHEN 'MTDT' THEN CONVERT(varchar(100), MatchDate, 101)
WHEN 'VNCD' THEN CAST(VenueID AS varchar(100))
WHEN 'HOST' THEN HostingTeam
WHEN 'VSTR' THEN VisitingTeam
WHEN 'HSTF' THEN CAST(HostFouls AS varchar(100))
WHEN 'VSTF' THEN CAST(VisitorFouls AS varchar(100))
WHEN 'HSTG' THEN CAST(HostGoals AS varchar(100))
WHEN 'VSTG' THEN CAST(VisitorGoals AS varchar(100))
WHEN 'RSLT' THEN CASE WHEN HostGoals > VisitorGoals THEN 'HWON' WHEN VisitorGoals > HostGoals THEN 'VWON' ELSE 'DRAW' END
END AS KeyValue
FROM @MySampleTable mst
CROSS JOIN (
SELECT 'MTDT' AS KeyCode, 1 AS KeySeq UNION ALL
SELECT 'VNCD', 2 UNION ALL
SELECT 'HOST', 3 UNION ALL
SELECT 'VSTR', 4 UNION ALL
SELECT 'HSTF', 5 UNION ALL
SELECT 'VSTF', 6 UNION ALL
SELECT 'HSTG', 7 UNION ALL
SELECT 'VSTG', 8 UNION ALL
SELECT 'RSLT', 9
) AS KeyCodes
ORDER BY MatchID, KeySeq
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Borrowing from the code in the CASE that was thoughtfully provided by Scott, I offer another alternative:
SELECT MatchID, KeyCode, KeyValue
FROM @MySampleTable a
CROSS APPLY
(
VALUES
('MTDT', CONVERT(varchar(100), MatchDate, 101))
,('VNCD', CAST(VenueID AS varchar(100)))
,('HOST', HostingTeam)
,('VSTR', VisitingTeam)
,('HSTF', CAST(HostFouls AS varchar(100)))
,('VSTF', CAST(VisitorFouls AS varchar(100)))
,('HSTG', CAST(HoastGoals AS varchar(100)))
,('VSTG', CAST(VisitorGoals AS varchar(100)))
,('RSLT', CASE WHEN HoastGoals > VisitorGoals THEN 'HWON' WHEN VisitorGoals > HoastGoals THEN 'VWON' ELSE 'DRAW' END)
) b (KeyCode, KeyValue)
Note that the column HoastGoals is misspelled the same as in your TABLE declaration.
An explanation of the CROSS APPLY VALUES approach to UNPIVOT that I used here is provided in the first article in my signature links.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Thanks, Scott & Dwain.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply