Click here to Skip to main content
15,123,923 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table like below.


Date	        Day     Values
2020-08-30	Sun	NULL
2020-08-31	Mon	NULL
2020-09-01	Tue	1
2020-09-02	Wed	1
2020-09-03	Thu	1
2020-09-04	Fri	1
2020-09-05	Sat	NULL
2020-09-06	Sun	NULL
2020-09-07	Mon	9
2020-09-08	Tue	9
2020-09-09	Wed	9
2020-09-10	Thu	9
2020-09-11	Fri	9
2020-09-12	Sat	NULL


This has to be formatted like this. Can some one help me please?

Sun	                Mon	                Tue	        Wed
2020-08-30->NULL	2020-08-31->NULL	2020-09-01-> 1	2020-09-02-> 1
2020-09-06->NULL	2020-09-07->9	        2020-09-08-> 9	2020-09-09-> 9


What I have tried:

I have tried pivot but did not work. Please help
Posted
Updated 22-Sep-20 3:24am

1 solution

Try this:

SQL
SELECT WeekOfYear, [Sun] = MAX(CASE WHEN DayOfWeek=1 THEN NewVal ELSE NULL END),
  [Mon] = MAX(CASE WHEN DayOfWeek=2 THEN NewVal ELSE NULL END), 
  [Tue] = MAX(CASE WHEN DayOfWeek=3 THEN NewVal ELSE NULL END), 
  [Wed] = MAX(CASE WHEN DayOfWeek=4 THEN NewVal ELSE NULL END),
  [Thu] = MAX(CASE WHEN DayOfWeek=5 THEN NewVal ELSE NULL END), 
  [Fri] = MAX(CASE WHEN DayOfWeek=6 THEN NewVal ELSE NULL END), 
  [Sat] = MAX(CASE WHEN DayOfWeek=7 THEN NewVal ELSE NULL END)
FROM
(
  SELECT DATEPART(DW, [Date]) DayOfWeek,
    DATEPART(WK, [Date]) WeekOfYear,
    [Day],
    CONCAT([Date], '->', COALESCE([Values], 0)) AS [NewVal] 
  FROM MyTable
) T
GROUP BY WeekOfYear


db<>fiddle[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900