October 23, 2014 at 2:16 pm
Hi Gurus - I have a table that I am trying to query to display all data connected to a id in one row. What is the best way to achieve this?
something like this:
ID| CRS1 | CRS2 | CRS3| CRS4 ....
1 | A101 | A102 | A103 | A104 ....
CREATE TABLE Class
(
id int,
yr int,
trm varchar(2),
crs varchar(30)
)
INSERT INTO dbo.Class
(dbo.Class.id,dbo.Class.yr,dbo.Class.trm,dbo.Class.crs)
VALUES
(1, 2014, 'F', 'A101' ),
(1, 2014, 'F', 'A102' ),
(1, 2014, 'F', 'A103' ),
(1, 2014, 'F', 'A104' ),
(1, 2014, 'F', 'A105' ),
(1, 2014, 'F', 'A106' ),
(1, 2014, 'F', 'A107' ),
(1, 2014, 'F', 'A108' ),
(2, 2014, 'F', 'A101' ),
(2, 2014, 'F', 'A102' ),
(2, 2014, 'F', 'A103' ),
(2, 2014, 'F', 'A104' ),
(2, 2014, 'F', 'A105' ),
(2, 2014, 'F', 'A106' ),
(2, 2014, 'F', 'A107' ),
(2, 2014, 'F', 'A108' )
October 23, 2014 at 7:48 pm
Sorry this is a bit of a "It Depends" answer. If you are only going to have up to eight courses then the following will do what you want.
WITH SequenceRows AS (
SELECT ID, Yr, Trm, Crs, ROW_NUMBER() OVER (PARTITION BY ID, Yr, Trm ORDER BY crs) Seq
FROM Class
)
SELECT ID, Yr, Trm,
MAX(CASE WHEN Seq = 1 THEN CRS END) CRS1,
MAX(CASE WHEN Seq = 2 THEN CRS END) CRS2,
MAX(CASE WHEN Seq = 3 THEN CRS END) CRS3,
MAX(CASE WHEN Seq = 4 THEN CRS END) CRS4,
MAX(CASE WHEN Seq = 5 THEN CRS END) CRS5,
MAX(CASE WHEN Seq = 6 THEN CRS END) CRS6,
MAX(CASE WHEN Seq = 7 THEN CRS END) CRS7,
MAX(CASE WHEN Seq = 8 THEN CRS END) CRS8
FROM SequenceRows
GROUP BY ID, Yr, Trm
Otherwise if you have an uncertain amount of course per ID, then you may want to do a dynamic cross tab[/url] as described in the article by Jeff Moden. The first part[/url] of the article describes doing a cross tab query.
October 24, 2014 at 1:06 am
If the data doesn't need to be in seperate columns, you can just concatenate the row values (and this works better if there is no fixed number of values):
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 24, 2014 at 7:48 am
mickyT (10/23/2014)
Sorry this is a bit of a "It Depends" answer. If you are only going to have up to eight courses then the following will do what you want.
WITH SequenceRows AS (SELECT ID, Yr, Trm, Crs, ROW_NUMBER() OVER (PARTITION BY ID, Yr, Trm ORDER BY crs) Seq
FROM Class
)
SELECT ID, Yr, Trm,
MAX(CASE WHEN Seq = 1 THEN CRS END) CRS1,
MAX(CASE WHEN Seq = 2 THEN CRS END) CRS2,
MAX(CASE WHEN Seq = 3 THEN CRS END) CRS3,
MAX(CASE WHEN Seq = 4 THEN CRS END) CRS4,
MAX(CASE WHEN Seq = 5 THEN CRS END) CRS5,
MAX(CASE WHEN Seq = 6 THEN CRS END) CRS6,
MAX(CASE WHEN Seq = 7 THEN CRS END) CRS7,
MAX(CASE WHEN Seq = 8 THEN CRS END) CRS8
FROM SequenceRows
GROUP BY ID, Yr, Trm
Otherwise if you have an uncertain amount of course per ID, then you may want to do a dynamic cross tab[/url] as described in the article by Jeff Moden. The first part[/url] of the article describes doing a cross tab query.
Thanks MikeyT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply