SQL Group By and then map out if values exists

Question

Suppose I have a table called Abilities that look something along the lines of

DayOfWeek	Action	Strength
Sunday	Walk	Weak
Sunday	Walk	Strong
Sunday	Run	Strong
Monday	Walk	Strong

I want to be able to do a group by the Action and Strength, and then check if the entry exists for each day of the week:

Action	Strength	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Walk	Weak	Y	N	N	N	N	N	N
Walk	Strong	Y	Y	N	N	N	N	N
Run	Strong	Y	N	N	N	N	N	N

I heard a Pivot might accomplish this, but I was wondering if there were any options available? Thanks.

score 2 · Accepted Answer · answered Mar 20 '23 at 05:59

I heard a Pivot might accomplish this, but I was wondering if there were any options available?

You would be able to do this like below :

SELECT 
  Action,
  Strength,
  MAX(CASE WHEN DayOfWeek = 'Sunday' THEN 'Y' ELSE 'N' END) AS Sunday,
  MAX(CASE WHEN DayOfWeek = 'Monday' THEN 'Y' ELSE 'N' END) AS Monday,
  MAX(CASE WHEN DayOfWeek = 'Tuesday' THEN 'Y' ELSE 'N' END) AS Tuesday,
  MAX(CASE WHEN DayOfWeek = 'Wednesday' THEN 'Y' ELSE 'N' END) AS Wednesday,
  MAX(CASE WHEN DayOfWeek = 'Thursday' THEN 'Y' ELSE 'N' END) AS Thursday,
  MAX(CASE WHEN DayOfWeek = 'Friday' THEN 'Y' ELSE 'N' END) AS Friday,
  MAX(CASE WHEN DayOfWeek = 'Saturday' THEN 'Y' ELSE 'N' END) AS Saturday
FROM Abilities
GROUP BY Action, Strength;

score 1 · Answer 2 · answered Mar 20 '23 at 06:49

Try this:

DROP TABLE IF EXISTS #TempTable;

CREATE TABLE #TempTable
(
    [DayOfWeek] VARCHAR(10),
    [Action] VARCHAR(10),
    Strength VARCHAR(10)
)

INSERT INTO #TempTable ([DayOfWeek], [Action], Strength)
VALUES ('Sunday', 'Walk', 'Weak'),
       ('Sunday', 'Walk', 'Strong'),
       ('Sunday', 'Run', 'Strong'),
       ('Monday', 'Walk', 'Strong')

SELECT *
FROM
(
    SELECT [DayOfWeek], [Action], Strength, 'Y' as [value]
    FROM #TempTable
) DS
PIVOT
(
    MAX([value]) FOR [DayOfWeek] IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])
) PVT

Pivot was a solution I was thinking of trying, but was wondering if there were other solutions. Thanks a lot for this. — lclankyo, Mar 20 '23 at 14:41

Zd Wu · Answer 3 · 2023-03-20T07:37:23.430

    --CREATE TABLE         
        CREATE TABLE TEMP AS 
         (SELECT 'Sunday' AS DAYOFWEEK, 'Walk' AS ACTION, 'Weak' AS STRENGTH
            FROM DUAL
          UNION ALL
          SELECT 'Sunday', 'Walk', 'Strong'
            FROM DUAL
          UNION ALL
          SELECT 'Sunday', 'Run', 'Strong'
            FROM DUAL
          UNION ALL
          SELECT 'Monday', 'Walk', 'Strong'
            FROM DUAL);
            
        
    --SQL
    SELECT ACTION,
           STRENGTH,
           NVL("'Monday'", 'N') AS "Monday",
           NVL("'Sunday'", 'N') AS "Sunday",
           NVL("'XX'", 'N') AS "XX"
      FROM (select *
              FROM (SELECT DAYOFWEEK, ACTION, STRENGTH, 'Y' AS Y FROM TEMP)
            PIVOT(MAX(Y)
               FOR DAYOFWEEK IN('Monday', 'Sunday', 'XX')))

-- result is like this 
    
ACTION STRENGTH Monday Sunday XX
------ -------- ------ ------ --
Run    Strong   N      Y      N
Walk   Strong   Y      Y      N
Walk   Weak     N      Y      N

written in ORACLE PL/SQL. However,Using "CASE WHEN" &"GROUP BY "is much easier to understand,in my opinion.

Not super-helpful since the question is for sql server. – siggemannen Mar 20 '23 at 08:58 — siggemannen, Mar 20 '23 at 08:58

SQL Group By and then map out if values exists

3 Answers3