My data-table is this:
| Weektype | Weekday | Time |
|---|---|---|
| 1 | 1 | 0800-1400 |
| 1 | 2 | 0900-1500 |
| 1 | 3 | 0800-1500 |
| 1 | 4 | 0800-1530 |
| 6 | 5 | 0800-1300 |
| 7 | 5 | 0900-1400 |
The weekday represent mon to fri.
I want the output to be like this:
| Weektype | Mon | Tue | Wed | Thu | Fri |
|---|---|---|---|---|---|
| 1 | 0800-1400 | 0900-1500 | 0800-1500 | 0800-1530 | |
| 6 | 0800-1300 | ||||
| 7 | 0900-1400 |
Build Example table:
Create table #tbl1 (Weektype INT, Weekday varchar(3),Time varchar(20))
Insert into #tbl1 (Weektype,Weekday,Time)
values (1,'1','0800-1400'),(1,'2','0900-1500'),(1,'3','0800-1500'),(1,'4','0800-1530'),(6,'5','0800-1300'),(7,'5','0900-1400')
My query
Select Distinct
org.Weektype
,day1.Time as Monday
,day2.Time as Tuesday
,day3.Time as Wednesday
,day4.Time as Thursday
,day5.Time as Fri
from #tbl1 org
cross join #tbl1 Week on Week.Weektype = org.Weektype
left join #tbl1 day1 on day1.Weekday = Week.Weekday AND Week.Weekday = 1
Left join #tbl1 day2 on day2.Weekday = Week.Weekday AND Week.Weekday = 2
Left join #tbl1 day3 on day3.Weekday = Week.Weekday AND Week.Weekday = 3
Left join #tbl1 day4 on day4.Weekday = Week.Weekday AND Week.Weekday = 4
Left join #tbl1 day5 on day5.Weekday = Week.Weekday AND Week.Weekday = 5
group by org.Weektype
Cleaning up
Drop table #tbl1
My result:
| Weektype | Monday | Tuesday | Wednesday | Thursday | Fri |
|---|---|---|---|---|---|
| 1 | NULL | NULL | NULL | 0800-1530 | NULL |
| 1 | NULL | NULL | 0800-1500 | NULL | NULL |
| 1 | NULL | 0900-1500 | NULL | NULL | NULL |
| 1 | 0800-1400 | NULL | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL | NULL | 0800-1300 |
| 6 | NULL | NULL | NULL | NULL | 0900-1400 |
| 7 | NULL | NULL | NULL | NULL | 0800-1300 |
| 7 | NULL | NULL | NULL | NULL | 0900-1400 |
You can use aggregate function like MAX
Select
org.Weektype
,MAX(day1.Time) as Monday
,MAX(day2.Time) as Tuesday
,MAX(day3.Time) as Wednesday
,MAX(day4.Time) as Thursday
,MAX(day5.Time) as Fri
from #tbl1 org
cross join #tbl1 Week on Week.Weektype = org.Weektype
left join #tbl1 day1 on day1.Weekday = Week.Weekday AND Week.Weekday = 1
Left join #tbl1 day2 on day2.Weekday = Week.Weekday AND Week.Weekday = 2
Left join #tbl1 day3 on day3.Weekday = Week.Weekday AND Week.Weekday = 3
Left join #tbl1 day4 on day4.Weekday = Week.Weekday AND Week.Weekday = 4
Left join #tbl1 day5 on day5.Weekday = Week.Weekday AND Week.Weekday = 5
group by org.Weektype
But ou can simpilf pou approach
Select
org.Weektype
,MAX(CASE WHEN org.Weekday = 1 THEN org.Time ELSe NULL END ) as Monday
,MAX(CASE WHEN org.Weekday = 2 THEN org.Time ELSe NULL END ) as Tuesday
,MAX(CASE WHEN org.Weekday = 3 THEN org.Time ELSe NULL END ) as Wednesday
,MAX(CASE WHEN org.Weekday = 4 THEN org.Time ELSe NULL END ) as Thursday
,MAX(CASE WHEN org.Weekday = 5 THEN org.Time ELSe NULL END ) as Frida
from #tbl1 org
group by org.Weektype
| Weektype | Monday | Tuesday | Wednesday | Thursday | Frida |
|---|---|---|---|---|---|
| 1 | 0800-1400 | 0900-1500 | 0800-1500 | 0800-1530 | null |
| 6 | null | null | null | null | 0800-1300 |
| 7 | null | null | null | null | 0900-1400 |
SELECT Weektype,
[1] as Monday, [2] as Tuesday, [3] as Wednesday, [4] as Thursday, [5] as Frida
FROM
#tbl1
PIVOT
(
MAX(Time)
FOR Weekday IN ([1], [2], [3], [4], [5])
) AS PivotTable;
| Weektype | Monday | Tuesday | Wednesday | Thursday | Frida |
|---|---|---|---|---|---|
| 1 | 0800-1400 | 0900-1500 | 0800-1500 | 0800-1530 | null |
| 6 | null | null | null | null | 0800-1300 |
| 7 | null | null | null | null | 0900-1400 |