About SQL pivot table

Question

I'm trying to figure out how to produce this result in SQL using pivot table Sorted by SubID

units	Sub	SubCode	AM	PM
3	Math	M2201	Monday / 7:00AM-8:00AM	Tuesday / 1:00PM-2:00PM
3	Science	S2203	Monday / 9:00AM-10:00AM	Tuesday / 3:00PM-4:00PM
3	Comp (lab)	C2203	Friday / 9:00AM-10:00AM	Wednesday / 3:00PM-4:00PM
2	Comp (lec)	C2203	Thursday / 9:00AM-10:00AM Friday / 7:00AM-8:00AM	Tuesday / 3:00PM-4:00PM

Originally, I have 3 tables where I pull out data.

table SetSub

ssID	AY	Prog	YLev	Sem	SubCode
1	2022-2023	Intermediate	3	2	M2201
2	2022-2023	Intermediate	3	2	S2203
2	2022-2023	Intermediate	3	2	C2203

table Sched

schedID	Prog	Sem	SubCode	Sub	Units	Shift	SubType	Day	Sched	isLecLab
1	Intermediate	2	M2201	Math	3	AM	Lec	Monday	7:00AM-8:00AM	0
2	Intermediate	2	M2201	Math	3	PM	Lec	Tuesday	1:00PM-2:00PM	0
3	Intermediate	2	S2203	Science	3	AM	Lec	Monday	9:00AM-10:00AM	0
4	Intermediate	2	S2203	Science	3	PM	Lec	Tuesday	3:00PM-4:00PM	0
5	Intermediate	2	C2203	Comp	2	AM	Lec	Thursday	9:00AM-10:00AM	1
6	Intermediate	2	C2203	Comp	2	AM	Lec	Friday	7:00AM-8:00AM	1
7	Intermediate	2	C2203	Comp	2	PM	Lec	Tuesday	3:00PM-4:00PM	1
8	Intermediate	2	C2203	Comp	3	AM	Lab	Friday	9:00AM-10:00AM	1
9	Intermediate	2	C2203	Comp	3	PM	Lab	Wednesday	3:00PM-4:00PM	1

table Subjects

subid	Sub	SubCode	Units	isLecLab
1	Math	M2201	3	0
2	Science	S2203	3	1
3	Comp	C2203	5	0

Added an image since table get messed up upon saving the post

But created a new table for this.

subid	units	sub	UserCode	Shift	Sched
1	3	Math	M2201	AM	Monday / 7:00AM-8:00AM
1	3	Math	M2201	PM	Tuesday / 1:00PM-2:00PM
2	3	Science	S2203	AM	Monday / 9:00AM-10:00AM
2	3	Science	S2203	PM	Tuesday / 3:00PM-4:00PM
3	3	Comp (lab)	C2203	AM	Friday / 9:00AM-10:00AM
3	2	Comp (lab)	C2203	PM	Wednesday / 3:00PM-4:00PM
3	3	Comp (lec)	C2203	AM	Thursday / 9:00AM-10:00AM
3	2	Comp (lec)	C2203	PM	Tuesday / 3:00PM-4:00PM
3	2	Comp (lec)	C2203	PM	Friday / 7:00AM-8:00AM

I tried several queries and the closest I've got is this

units	Sub	Code	AM	PM
3	Math	M2201	Monday / 7:00AM-8:00AM	Tuesday / 1:00PM-2:00PM
3	Science	S2203	Monday / 9:00AM-10:00AM	Tuesday / 3:00PM-4:00PM
3	Comp (lab)	C2203	Thursday / 9:00AM-10:00AM	Tuesday / 3:00PM-4:00PM
3	Comp (lab)	C2203	Friday / 9:00AM-10:00AM	Wednesday / 3:00PM-4:00PM
2	Comp (lec)	C2203	Thursday / 9:00AM-10:00AM	Tuesday / 3:00PM-4:00PM
2	Comp (lec)	C2203	Friday / 9:00AM-10:00AM	Wednesday / 3:00PM-4:00PM

Second data for AM of comp (lec) didn't appear. Here's the code I've tried

select a.usercode, a.sub, a.Units, a.am, b.pm, a.Schedid from 
    (select * from 
        (select distinct subid, usercode, sub, units, shift, sched from Table1 where shift= 'am') as src 
        pivot (max(sched) for shift in ("am")) as pvt ) as A 
inner join 
    (select * from 
        (select distinct subid, usercode, sub, units, shift, sched from table1 where shift= 'pm') as src2 
        pivot (max(sched) for shift in ("pm")) as pvt2 ) as B on a.shift= b.shift

What about `select A.units as units, A.sub as Sub, A.UserCode as Code, A.Sched as AM, B.Sched as PM from data A join data B on A.sub = B.sub and A.UserCode = B.UserCode where A.Shift = 'AM' and B.Shift = 'PM';` https://www.db-fiddle.com/f/5y3mTqaQKKnQEa2HGL54be/1 — Jerry Jeremiah, Apr 05 '22 at 04:48
You should have mention that in your initial question. Update your question, include the sample data together with the expected result for that scenario. — Squirrel, Apr 05 '22 at 06:44
@JerryJeremiah for some reasons, it didn't work as i expected. only tried adding some rows in db-fiddle link you provided. :( — ran-de-vouz, Apr 05 '22 at 06:48
@Squirrel sorry about that. it just happened that i overlook some data while checking. — ran-de-vouz, Apr 05 '22 at 06:57
@CaiusJard not really. i just need to have the result the same as stated. — ran-de-vouz, Apr 05 '22 at 07:05
@CaiusJard _"but created a new table for this"_ which i mean is that i created a new table where i will pull those data for easier coding but still cant figure it out. — ran-de-vouz, Apr 05 '22 at 07:18
Easier for you maybe (but then again you did embark on a process and get stuck..), but not necessarily for us; we can operate with your original 3 tables - post them so we have the luxury of deciding if to proceed with your merged table, or the originals. Making tables unnecessarily is a data management headache — Caius Jard, Apr 05 '22 at 07:18
In your desired output, why does Comp lec have 2 units, but Comp lab have 3? What is the logic that drives the contents of the Units column? — Caius Jard, Apr 05 '22 at 07:20
@CaiusJard will add the tables regarding that later. About the divided subject units, that's what is ask to be the output. Originally, it was 5 units (combine lec and lab). I, too, can't see the logic why was it divided as units for lec and lab. — ran-de-vouz, Apr 05 '22 at 07:35
I mean your lab has 3,2 as units and the lec has 3,2,2. Why does the output choose 2 for the lec and 3 for the lab? It's not consistently min, max, mode or any other pattern i can see. It looks like some exception for lec.. — Caius Jard, Apr 05 '22 at 07:38

Caius Jard · Answer 1 · 2022-04-05T07:45:22.477

Conditional agg will perform a pivot, and it's eaier to perform more advanced operations using it than PIVOT does, so it's a pattern worth learning.

To see how a conditional agg works, remove the GROUP BY and any mention of a MIN/MAX/STRING_AGG or other agregating operation. It makes it easier to see that a vertical arrangement of data:

  A, 1
  B, 2
  C, 1

Becomes diagonal when CASE WHEN'd:

  --CASE WHEN letter = 'A' then number end as a,
  --CASE WHEN letter = 'B' then number end as b,
  --CASE WHEN letter = 'C' then number end as c,

  A, B, C
  1, -, -
  -, 2, -
  -, -, 3

The GROUP/MAX then flattens the nulls out, so the "diagonal" data is fully rotated to horizontal

  --MAX(CASE WHEN letter = 'A' then number end) as a,
  --MAX(CASE WHEN letter = 'B' then number end) as b,
  --MAX(CASE WHEN letter = 'C' then number end) as c,
  A, B, C
  1, 2, 3

In your requirement, using STRING_AGG allows multiple values per cell rather than just one

This form is for use on your joined table because I cannot make assumptions about the 3 tables that generated it (no detail)

Something like this should work for SQLS..

SELECT 
  CASE WHEN Sub = 'Comp (lec)' THEN MIN(Units) ELSE MAX(Units) END as Units,
  Sub,
  MAX(UserCode) as UserCode,
  STRING_AGG(CASE WHEN Shift = 'AM' THEN Sched END, CHAR(10)) as AM,
  STRING_AGG(CASE WHEN Shift = 'PM' THEN Sched END, CHAR(10)) as PM
FROM
  t
GROUP BY
  Sub

..but it'd be good to see the source tables/the query that generated t. Getting the data in the exact order per cell (making sure that the cell says "Thursday..Friday" rather then "Friday..Thursday" might be really messy. It would help if these things were numeric/dates somewhere. Post the original data so we can see if it helps)

tried it and noticed an error "string_agg" is not a recognized built-in function name. it seems that it was for sql server 2017 onwards. I was using sql 2012. btw, thank you for pointing out my mistakes regarding my post. — ran-de-vouz, Apr 05 '22 at 07:43
Oof, that's a shame. STRING_AGG is usually done by some ugly STUFF FOR XML PATH in ancient versions of SQLS. Upgrade your DB or take a look at https://stackoverflow.com/questions/56575019/string-agg-is-not-recognized-built-in-function-name-sql-server-12-0 or for a look at different ways and their performances see https://www.codeproject.com/articles/691102/string-aggregation-in-the-world-of-sql-server — Caius Jard, Apr 05 '22 at 07:47
Incidentally, here it is working in a modern SQLS: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=68d6ced66bba61d35c82ede1aca15c79 — Caius Jard, Apr 05 '22 at 07:51

About SQL pivot table

1 Answers1