CODEWITHSUNDEEP
sqlsql-server
Vhinchies
Vhinchies

Reputation: 53

How to group this table properly?

Here's my dataset with expected out. But I am having issues doing it.

Also, I attached the query I created and the output is not correct. could you please point me to where my mistake is?

DROP TABLE #TEMP_ROLES
DROP TABLE #ORG_ROLES
CREATE TABLE #TEMP_ROLES
(
    ID INT,
    DESCRIPTIONS CHAR(15)
)

INSERT INTO #TEMP_ROLES VALUES (1, 'ROLE 1')
INSERT INTO #TEMP_ROLES VALUES (2, 'ROLE 2')
INSERT INTO #TEMP_ROLES VALUES (3, 'ROLE 3')
INSERT INTO #TEMP_ROLES VALUES (4, 'ROLE 4')
INSERT INTO #TEMP_ROLES VALUES (5, 'ROLE 5')

CREATE TABLE #ORG_ROLES
(
    ID INT,
    NAME CHAR(15),
)

INSERT INTO #ORG_ROLES VALUES (1, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (2, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (3, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (1, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (2, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (3, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (4, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (5, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (1, 'NAME_3')

This is the query I created but the output is incorrect.

SELECT NAME,
CASE WHEN TR.ID = 1 THEN 'YES' ELSE 'NO'  END  AS ROLE_1,
CASE WHEN TR.ID = 2 THEN 'YES' ELSE 'NO'  END  AS ROLE_2,
CASE WHEN TR.ID = 3 THEN 'YES' ELSE 'NO' END  AS ROLE_3,
CASE WHEN TR.ID = 4 THEN 'YES' ELSE 'NO'  END  AS ROLE_4,
CASE WHEN TR.ID = 5 THEN 'YES' ELSE 'NO' END  AS ROLE_5
FROM #TEMP_ROLES TR
LEFT JOIN #ORG_ROLES R ON TR.ID = R.ID

Expected output:

NAME           ROLE_1   ROLE_2  ROLE_3  ROLE_4  ROLE_5
NAME_1          YES      YES      YES     NO      NO
NAME_2          YES      YES      NO      YES     YES
NAME_3          YES      NO       NO      NO      NO

Upvotes: 3

Views: 49

Answers (4)

Excelnoobster
Excelnoobster

Reputation: 129

I've never been a fan of the use of MAX in these sort of things, as it implies there can be more than one value, when that is not the case. I prefer to use sub-selects as it is specific in what it is looking for (ie one value), and has the added benefit of resulting in an exception if there is more than one value. So should there be duplicates, you'll soon know about it! It's not pretty, but it's unambiguous ...

SELECT TR.NAME, ISNULL( ( SELECT 'YES' FROM #TEMP_ROLES TR1 WHERE TR1.ID = TR.ID AND TR1.NAME = TR.NAME) ,'NO') AS ROLE_1, ... ETC FROM ( SELECT DISTINCT TR0.ID, TR0.NAME FROM #TEMP_ROLES TR0) AS TR

Upvotes: 0

CR7SMS
CR7SMS

Reputation: 2584

Assuming that the ID number does not always correspond to the name and description, the below query should give the expected output:

  SELECT NAME,
        CASE WHEN SUM(CASE WHEN DESCRIPTIONS='ROLE 1' then 1 else 0 end)>0 then 'Yes' else 'No' end as Role_1,
        CASE WHEN SUM(CASE WHEN DESCRIPTIONS='ROLE 2' then 1 else 0 end)>0 then 'Yes' else 'No' end as Role_2,
        CASE WHEN SUM(CASE WHEN DESCRIPTIONS='ROLE 3' then 1 else 0 end)>0 then 'Yes' else 'No' end as Role_3,
        CASE WHEN SUM(CASE WHEN DESCRIPTIONS='ROLE 4' then 1 else 0 end)>0 then 'Yes' else 'No' end as Role_4,
        CASE WHEN SUM(CASE WHEN DESCRIPTIONS='ROLE 5' then 1 else 0 end)>0 then 'Yes' else 'No' end as Role_5
FROM #TEMP_ROLES TR
LEFT JOIN #ORG_ROLES R ON TR.ID = R.ID
GROUP BY NAME

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

You're almost there - you just need to add aggregation:

SELECT  NAME,
        MAX(CASE WHEN TR.ID = 1 THEN 'YES' ELSE 'NO'  END) AS ROLE_1,
        MAX(CASE WHEN TR.ID = 2 THEN 'YES' ELSE 'NO'  END)  AS ROLE_2,
        MAX(CASE WHEN TR.ID = 3 THEN 'YES' ELSE 'NO' END)  AS ROLE_3,
        MAX(CASE WHEN TR.ID = 4 THEN 'YES' ELSE 'NO'  END)  AS ROLE_4,
        MAX(CASE WHEN TR.ID = 5 THEN 'YES' ELSE 'NO' END)  AS ROLE_5
FROM #TEMP_ROLES TR
LEFT JOIN #ORG_ROLES R ON TR.ID = R.ID
GROUP BY NAME

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need some aggregation. And you don't need the JOIN:

SELECT R.NAME,
       MAX(CASE WHEN R.ID = 1 THEN 'YES' ELSE 'NO' END) AS ROLE_1,
       MAX(CASE WHEN R.ID = 2 THEN 'YES' ELSE 'NO' END) AS ROLE_2,
       MAX(CASE WHEN R.ID = 3 THEN 'YES' ELSE 'NO' END) AS ROLE_3,
       MAX(CASE WHEN R.ID = 4 THEN 'YES' ELSE 'NO' END) AS ROLE_4,
       MAX(CASE WHEN R.ID = 5 THEN 'YES' ELSE 'NO' END) AS ROLE_5
FROM #ORG_ROLES R 
GROUP BY R.NAME

Upvotes: 1

Related Questions