How can I convert this PIVOT sql to CASE WHEN-CodePudding

I want to convert this PIVOT to CASE WHEN since I read that PIVOT is slow against a large database. Is it possible? I tried converting it BUT failed, it is displaying all the cmdocumentdefn.code COUNT which is 50. I want to count the code by weekday i.e.

MON  TUE  WED .... SUN
8     2    4        1

The query is:

SELECT * FROM
    (SELECT 
        cmdocumentdefn.code, cmdocumentdefn.description 'description',
        CASE DatePart(weekday,cmrevisionaddress.issueddate)
        WHEN 1 THEN 'SUN'
        WHEN 2 THEN 'MON'
        WHEN 3 THEN 'TUE' 
        WHEN 4 THEN 'WED'
        WHEN 5 THEN 'THU'
        WHEN 6 THEN 'FRI'
        WHEN 7 THEN 'SAT'
        END  'dayx'    
    FROM cmdocumentdefn
        INNER JOIN
        cmdocument
        ON cmdocumentdefn.cmdocumentdefn = cmdocument.cmdocumentdefn
        INNER JOIN cmrevisionaddress
        ON cmdocument.cmdocument = cmrevisionaddress.cmdocument WHERE cmdocumentdefn.code = 'AF') x
    PIVOT(COUNT(dayx) FOR dayx IN([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) pvt

CodePudding user response：

--Data to work with
DECLARE @data TABLE (DayInWeek int, SomeData nvarchar(100))
INSERT INTO @data (DayInWeek, SomeData) VALUES (1, '1st')
INSERT INTO @data (DayInWeek, SomeData) VALUES (1, '2nd')
INSERT INTO @data (DayInWeek, SomeData) VALUES (1, '3rd')
INSERT INTO @data (DayInWeek, SomeData) VALUES (2, '4th')
INSERT INTO @data (DayInWeek, SomeData) VALUES (2, '5th')
INSERT INTO @data (DayInWeek, SomeData) VALUES (2, '6th')
INSERT INTO @data (DayInWeek, SomeData) VALUES (2, '7th')
INSERT INTO @data (DayInWeek, SomeData) VALUES (5, '8th')

--Query you want
SELECT COUNT(*) OVER(Partition by DayInWeek) as CountedDays, SomeData,
    CASE DayInWeek
        WHEN 1 THEN 'SUN'
        WHEN 2 THEN 'MON'
        WHEN 3 THEN 'TUE' 
        WHEN 4 THEN 'WED'
        WHEN 5 THEN 'THU'
        WHEN 6 THEN 'FRI'
        WHEN 7 THEN 'SAT'
        END  'Day' 
FROM @data

You could use COUNT(*) OVER() to create subgroups of data which are then counted for each partition while holding the rest of the data without aggregation. This is the result I got for the example data:

CodePudding user response：

You can alternatively use the logic which contains conditional aggregation with SUM() such as

WITH d AS -- in order to generate fixed integers from 1 to 7 for the day of a week 
(
 SELECT 1 AS day_num
 UNION ALL
 SELECT day_num 1 FROM d WHERE day_num 1<=7
), cra AS
(
 SELECT DatePart(weekday, issueddate) AS day
   FROM d
   LEFT JOIN cmrevisionaddress
     ON DatePart(weekday, issueddate) = day_num
)
SELECT SUM(CASE WHEN day=2 THEN 1 ELSE 0 END) AS MON,
       SUM(CASE WHEN day=3 THEN 1 ELSE 0 END) AS TUE,
       SUM(CASE WHEN day=4 THEN 1 ELSE 0 END) AS WED,
       SUM(CASE WHEN day=5 THEN 1 ELSE 0 END) AS THU,
       SUM(CASE WHEN day=6 THEN 1 ELSE 0 END) AS FRI,
       SUM(CASE WHEN day=7 THEN 1 ELSE 0 END) AS SAT,
       SUM(CASE WHEN day=1 THEN 1 ELSE 0 END) AS SUN
  FROM cmdocumentdefn AS cdd
  JOIN cmdocument AS cd
    ON cdd.cmdocumentdefn = cd.cmdocumentdefn
  JOIN cra
    ON cd.cmdocument = cra.cmdocument
 WHERE cdd.code = 'AF'

Demo