January 6, 2017 at 3:59 pm
I have data for individuals and all the sports they are involved in. I need to return the user information on one row and then the sports they are involved in (up to 3) as additional columns. Here is sample data to represent what I'm working with:
CREATE TABLE #example
(
name VARCHAR(20),
city VARCHAR(20),
entryDate DATETIME,
sport VARCHAR(30)
)
INSERT INTO #example
(name, city, entryDate, sport)
VALUES
('Aaron', 'Gladstone', '8/27/2007', 'Track - Mens'),
('Aaron', 'Gladstone', '8/27/2007', 'Track - Mens - Indoor'),
('Aaron', 'Gladstone', '8/27/2007', 'Cross Country - Mens'),
('Bob', 'Plain', '9/1/2010', 'Wrestling'),
('Chris', 'Milwaukee', '1/15/2015', 'Baseball'),
('Chris', 'Milwaukee', '1/15/2015', 'Football'),
('Doug', 'Richmond', '5/22/2003', 'Volleyball'),
('Doug', 'Richmond', '5/22/2003', 'Cross Country - Womens'),
('Doug', 'Richmond', '5/22/2003', 'Track - Womens'),
('Doug', 'Richmond', '5/22/2003', 'Track - Womens - Indoor')
What I need to return should look like this (I'm using the | symbol to separate the fields in the example) :
Name | City | Entry Date | Sport 1 | Sport 2 | Sport 3
Aaron | Gladstone | 8/27/2007 | Track - Mens | Track - Mens - Indoor | Cross Country - Mens
Bob | Plain | 9/1/2010 | Wrestling
Chris | Milwaukee | 1/15/2015 | Baseball | Football
Doug | Richmond | 5/22/2003 | Volleyball | Cross Country - Womens | Track - Womens
January 6, 2017 at 10:30 pm
WITH cteSports AS (
SELECT * ,sn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY sport)
FROM #example
)
SELECT
cte.name
, cte.city
, cte.entryDate
, sport1 = MAX(CASE WHEN cte.sn = 1 THEN cte.sport ELSE NULL END)
, sport2 = MAX(CASE WHEN cte.sn = 2 THEN cte.sport ELSE NULL END)
, sport3 = MAX(CASE WHEN cte.sn = 3 THEN cte.sport ELSE NULL END)
FROM cteSports AS cte
GROUP BY cte.name, cte.city, cte.entryDate;
January 8, 2017 at 1:24 pm
I wanted to mention that this table structure doesn't follow 1st normal form?
If this is a database of your own design, I would suggest updating your tables to do so. This would mean, at minimum creating a Player and Sport table, as well as a link table between Player and Sport due to the Many to Many relationship.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2017 at 7:38 am
Thanks! This looks like it will do the trick. Just need to incorporate it with the actual table structure in the database.
January 9, 2017 at 7:41 am
This isn't my database design, however, the actual database itself is in 1st Normal form. To get a quick and easy example I posted this knowing I'd have to take any suggestions and retrofit them to the actual database I'm using. Good point, however, for future reference to go a little further in the quick example for temp data.
January 9, 2017 at 7:58 am
ehlinger (1/9/2017)
This isn't my database design, however, the actual database itself is in 1st Normal form. To get a quick and easy example I posted this knowing I'd have to take any suggestions and retrofit them to the actual database I'm using. Good point, however, for future reference to go a little further in the quick example for temp data.
That's fine, I just wanted to make sure 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 10, 2017 at 1:23 pm
My suggestion would be to use a PIVOT clause:
WITH cteSports AS (
SELECT * ,sn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY sport)
FROM #example
)
SELECT name, city, entryDate
, [1] AS sport1
, [2] AS sport2
, [3] AS sport3
FROM cteSports
PIVOT (MAX(sport) FOR sn IN ([1],[2],[3])) AS pvt
Why? Because it's probably more efficient and designed to do this kind of operation :-).
January 10, 2017 at 2:54 pm
JJMEIJER (1/10/2017)
My suggestion would be to use a PIVOT clause:
WITH cteSports AS (SELECT * ,sn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY sport)
FROM #example
)
SELECT name, city, entryDate
, [1] AS sport1
, [2] AS sport2
, [3] AS sport3
FROM cteSports
PIVOT (MAX(sport) FOR sn IN ([1],[2],[3])) AS pvt
Why? Because it's probably more efficient and designed to do this kind of operation :-).
Jeff Moden ran a series of tests of a crosstab vs a pivot[/url], and, at that time, the crosstab performed better in the vast majority of cases. Admittedly, that was several years ago, and they may have improved the performance of the pivot function.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 11, 2017 at 1:57 am
Thanks for your reply, I use the pivot quite often for management reporting so any insight is welcome.
Regards, JJ
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply