Need help getting row data into column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help getting row data into column

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-10-07 : 07:11:55

Hi

I'm running some SQL to get a field that is currently held in a row format in to a column format. see below:

SELECT distinct cl.stu_code,
MAX(CASE WHEN cl.Seq=1 THEN Course ELSE '' END) AS Course_01,
MAX(CASE WHEN cl.Seq=2 THEN Course ELSE '' END) AS Course_02,
MAX(CASE WHEN cl.Seq=3 THEN Course ELSE '' END) AS Course_03,
MAX(CASE WHEN cl.Seq=4 THEN Course ELSE '' END) AS Course_04

FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY stu_code ORDER BY Course) AS Seq,* FROM course_location)cl

GROUP BY cl.stu_code, cl.site, cl.mode

The data is currently held within a table in a field called stu_code and a field called course, people can be on multiple courses.

When i run the query the results I get are as follows:

Stu_Code Course_01 Course_02 Course_03 Course_04
10067183 CALSU1-08 _________ _________ _________
10068895 BSBK21-06 _________ _________ _________
10069198 BSBK21-07 _________ _________ _________
10069283 _________ BSBKe1-06 _________ _________
10069414 IFP6-0810 BDKF-0809 _________ _________
10069819 _________ BSBKe1-07 BSBK^1-06 _________
10069819 _________ CGCOMM210 _________ _________
10070735 CGNUM3105 CGIT31-05 _________ _________

The problem it is occasionally returning two lines against the one Stu_Code, with another course code on the next line. There are alos some people who have no data returned in the Course_01 field.

Its not doing it for all and there doesn't seem to be a pattern with it. What I need is one Stu_Code with all the relevant courses that the person is undertaking.

Can anybody help with this?

Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:20:44

may be this?

SELECT distinct cl.stu_code,
MAX(CASE WHEN cl.Seq=1 THEN Course ELSE '' END) AS Course_01,
MAX(CASE WHEN cl.Seq=2 THEN Course ELSE '' END) AS Course_02,
MAX(CASE WHEN cl.Seq=3 THEN Course ELSE '' END) AS Course_03,
MAX(CASE WHEN cl.Seq=4 THEN Course ELSE '' END) AS Course_04

FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY stu_code ORDER BY Course) AS Seq,* FROM course_location)cl

GROUP BY cl.stu_code, cl.site, cl.mode