Probaly a simple solution to this, but it elludes me. I have a SQL-Server (2017) table like this:

I want to transpose this table to this using T-sql:

I tried using PIVOT, but the aggregate MAX function only gives me one record per week for the wk column. What is the correct / best SQL solution?

This is what I tried:

select * from (select wk, subj, day from mytable ) d 
pivot (max(subj) for day in (mon, tue, wed, thu, fri, sat, sun)) piv; 

CodePudding user response：

This is just a simple pivot, or (what I and many others prefer) conditional aggregation:

SELECT wk,
       MAX(CASE day WHEN 'mon' THEN subj END) AS mon,
       MAX(CASE day WHEN 'tue' THEN subj END) AS tue,
       MAX(CASE day WHEN 'wed' THEN subj END) AS wed
FROM (VALUES(1,202225,'mon','subj1'),
            (2,202225,'mon','subj2'),
            (3,202225,'mon','subj3'),
            (1,202225,'tue','subj4'),
            (2,202225,'tue','subj5'),
            (1,202225,'wed','subj6'),
            (2,202225,'wed','subj7'),
            (1,202226,'mon','subj8'),
            (2,202226,'mon','subj9'),
            (1,202226,'tue','subj10'),
            (1,202226,'wed','subj11'),
            (2,202226,'wed','subj12'),
            (3,202226,'wed','subj13'))V(rn,wk,day,subj)
GROUP BY rn,
         wk
ORDER BY wk,
         rn;

db<>fiddle