sql from row to columns

Question

Please help me out with the following issue. want to convert row data into the columns based on value of "PeriodNum" column max column will up to period8... there are multiple teachers in the table...below is just sample of 2 teachers.

Sample of data:

id      teachername course_name               periodnum   courseid
------------------------------------------------------------------
14088   Smith, John 1; Physical Education 9  GYM  ABCD  1   10064
14088   Smith, John 2; Physical Education 9  GYM  ABCD  2   10064
14088   Smith, John 3; Physical Education 11  GYM  BD   3   10065
14088   Smith, John 5; Physical Education 11  GYM  AC   5   10065
14088   Smith, John 6; Physical Education 11  GYM  ABCD 6   10065
14088   Smith, John 7; Health 9  P373  ABCD 7   10059
14088   Smith, John 8; Physical Education 11  GYM  AC   8   10065
14088   Smith, John 8; Health 10  GYM  BD   8   10066
15411   Yong, Jerry 1; Science 6  ABCD  1   10078
15411   Yong, Jerry 2; Science 9  ABCD  2   10078
15411   Yong, Jerry 3; Science 11    BD 3   10078
15411   Yong, Jerry 5; Science 11   AC  5   10078
15411   Yong, Jerry 6; Science 11  ABCD 6   10078
15411   Yong, Jerry 7; Maths  P373  ABCD    7   10080
15411   Yong, Jerry 8; Biology 11 AC    8   10041

I want to have desired result to look like following:

id  teachername Period1 Period2 Period3 Period4 Period5 Period6 Period7 Period8
14088   Smith, John 1; Physical Education 9  GYM  ABCD  2; Physical Education 9  GYM  ABCD  3; Physical Education 11  GYM  BD   NULL    5; Physical Education 11  GYM  AC   6; Physical Education 11  GYM  ABCD 7; Health 9  P373  ABCD 8; Physical Education 11  GYM  AC + 8; Health 10  GYM  BD
15411   Yong, Jerry 1; Science 6  ABCD  2; Science 9  ABCD  3; Science 11    BD     5; Science 11   AC  6; Science 11  ABCD 7; Maths  P373  ABCD    8; Biology 11 AC

thanks...so much.

I've tried following query

 SELECT DISTINCT
    t.[id],
    t.[teachername]
        ,t1.course_name AS period1,t2.course_name AS period2,t3.course_name AS period3, t4.course_name AS period4, t5.course_name AS period5, t6.course_name AS period6,
         t7.course_name AS period7,t8.course_name AS period8 -- into ccs_test
    FROM @teacher t
        LEFT OUTER JOIN CompanyGrouped t1 ON t.[id]=t1.[id] AND t1.ColumnNumber=1 and  t.periodnum=1
        LEFT OUTER JOIN CompanyGrouped t2 ON t.[id]=t2.[id] AND t2.ColumnNumber=2 and t.periodnum=2
        LEFT OUTER JOIN CompanyGrouped t3 ON t.[id]=t3.[id] AND t3.ColumnNumber=3 and t.periodnum=3
        LEFT OUTER JOIN CompanyGrouped t4 ON t.[id]=t4.[id] AND t4.ColumnNumber=4 and t.periodnum=4
        LEFT OUTER JOIN CompanyGrouped t5 ON t.[id]=t5.[id] AND t5.ColumnNumber=5 and t.periodnum=5
        LEFT OUTER JOIN CompanyGrouped t6 ON t.[id]=t6.[id] AND t6.ColumnNumber=6 and t.periodnum=6
        LEFT OUTER JOIN CompanyGrouped t7 ON t.[id]=t7.[id] AND t7.ColumnNumber=7 and t.periodnum=7
        LEFT OUTER JOIN CompanyGrouped t8 ON t.[id]=t8.[id] AND t7.ColumnNumber=8 and t.periodnum=8

Regards, HT

Please format your code so it is actually readable, and provide queries you've already tried. — Siyual, Nov 26 '14 at 19:28
What database are you using since there are functions on some that do this — Abercrombieande, Nov 26 '14 at 19:28
possible duplicate of [Understanding PIVOT function in T-SQL](http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — AHiggins, Nov 26 '14 at 19:54
Thanks @AHiggins for pointing me to the thread...that solutions worked for me. — hets, Nov 26 '14 at 20:56

score 0 · Accepted Answer · answered Nov 26 '14 at 20:20

0

I brought the data into a table in SQL Server and was able to pivot to the results you were looking for.

SELECT ID
, TeacherName
,[1] as Period1
,[2] as Period2
,[3] as Period3
,[4] as Period4
,[5] as Period5
,[6] as Period6
,[7] as Period7
,[8] as Period8
FROM 
(SELECT ID, TeacherName, peridonum, course_name
FROM teacher) src
PIVOT
(MAX(course_name) FOR peridonum in ([1],[2],[3],[4],[5],[6],[7],[8])
) pvt

answered Nov 26 '14 at 20:20

Vinnie

3,889
1
26
29

Thanks @Vinnie my issue is resolved now... thanks so much again. – hets Nov 26 '14 at 20:57

sql from row to columns

1 Answers1