How do i pivot these?

I have a query that returns:
Dow Course
Monday Science
Monday Math
Tuesday LA
Tuesday Gym
Tuesday Math
Wednesday Science
Wednesday Math
Thursday Gym
Thursday LA
Thursday Science
Friday Math

How do I pivot these so that the data is returned this way?
Monday Tuesday Wednesday Thursday Friday
Science LA Science Gym Math
Math Gym Math LA
math Science

Thanks for your help.

Please provide:

  • table structure from the table(s) used, in the form of "create table" statement(s)
  • sample data in the form of "insert" statement(s)
  • your query
  • expected output from the sample data you provide

Please study PIVOT

CREATE TABLE [dbo].[TestCourses](

[Dow] [nvarchar](50) NOT NULL,

[Course] [nvarchar](50) NOT NULL

) ON [PRIMARY]

GO

INSERT INTO TestCourses (Dow, Course) VALUES ('Monday','Science')

INSERT INTO TestCourses (Dow, Course) VALUES ('Monday','Math')

INSERT INTO TestCourses (Dow, Course) VALUES ('Tuesday','LA')

INSERT INTO TestCourses (Dow, Course) VALUES ('Tuesday','Science')

INSERT INTO TestCourses (Dow, Course) VALUES ('Tuesday','Gym')

INSERT INTO TestCourses (Dow, Course) VALUES ('Wednesday','Science')

INSERT INTO TestCourses (Dow, Course) VALUES ('Wednesday','Math')

INSERT INTO TestCourses (Dow, Course) VALUES ('Wednesday','Gym')

INSERT INTO TestCourses (Dow, Course) VALUES ('Wednesday','LA')

INSERT INTO TestCourses (Dow, Course) VALUES ('Thursday','Science')

INSERT INTO TestCourses (Dow, Course) VALUES ('Thursday','LA')

INSERT INTO TestCourses (Dow, Course) VALUES ('Friday','Science')

INSERT INTO TestCourses (Dow, Course) VALUES ('Friday','LA')

INSERT INTO TestCourses (Dow, Course) VALUES ('Friday','Math')

SELECT * FROM

(

SELECT

Dow,

Course

FROM

TestCourses

) as Source

PIVOT (

MIN(course)

FOR dow in (Monday, Tuesday, Wednesday, Thursday, Friday)

) As pvt

Output should be: Column headers should be the day of the week and under them are listed all the courses for each day. My pivot query above only gives me one course for each day.

Ideally your table should have a column indicating the number/order of the lesson (so we'd know that on Monday, Science is the first lesson etc).

SQL Server doesn't guarantee the order of the rows, so this might not satisfy your needs:

with cte1
  as (select dow
            ,course
            ,row_number() over(partition by dow order by (select null)) as rn
        from dbo.testcourses
     )
    ,cte2
  as (select rn
        from cte1
       group by rn
     )
    ,cte3
  as (select dow
        from cte1
       group by dow
     )
select a.rn
      ,max(case when b.dow='Monday' then c.course else null end) as mon
      ,max(case when b.dow='Tuesday' then c.course else null end) as tue
      ,max(case when b.dow='Wednesday' then c.course else null end) as wed
      ,max(case when b.dow='Thursday' then c.course else null end) as thu
      ,max(case when b.dow='Friday' then c.course else null end) as fri
  from cte2 as a
       cross apply cte3 as b
       left outer join cte1 as c
                    on c.rn=a.rn
                   and c.dow=b.dow
 group by a.rn
 order by a.rn
;
2 Likes