SQL Server select multiple entries from a single column into a single row result

Question

I need to extract some system user data from table "userrole" into a configuration record, but these user permissions are held in a single column and identified by a different roleid.

So my userrole data table looks like this,

UserID  RoleID  Discriminator
int     int NVarChar
3483    1   Pathologist
3483    2   Histotech
3483    3   Configuration
3483    4   WebViewer
3484    1   Pathologist
3484    4   WebViewer
3485    1   Pathologist
3485    4   WebViewer
3487    1   Pathologist
3487    2   Histotech
3487    3   Configuration
3487    4   WebViewer
3488    1   Pathologist
3488    2   Histotech
3488    3   Configuration
3488    4   WebViewer

and my target result is

3483    Pathologist Histotech   Configuration   WebViewer
3484    Pathologist                             WebViewer
3484    Pathologist                             WebViewer
3487    Pathologist Histotech   Configuration   WebViewer

etc.

But every attempt at "grouping by" just still me multiple rows returned, for example

  select USERID
  ,(select Discriminator where roleid = 1) as Pathologist
  ,(select Discriminator where roleid = 2) as Histologist
  ,(select Discriminator where roleid = 3) as Configuration
  ,(select Discriminator where roleid = 4) as Web
  FROM [Workflow].[UserRole]
  group by  userid, RoleID, discriminator

gives

    USERID  Pathologist Histologist Configuration   Web
    3483    Pathologist NULL    NULL    NULL
    3483    NULL    Histotech   NULL    NULL
    3483    NULL    NULL    Configuration   NULL
    3483    NULL    NULL    NULL    WebViewer
    3484    Pathologist NULL    NULL    NULL
    3484    NULL    NULL    NULL    WebViewer
    3485    Pathologist NULL    NULL    NULL
    3485    NULL    NULL    NULL    WebViewer

Trying to use a DISTINCT or MIN function on the userid as suggested in SQL Query Multiple Columns Using Distinct on One Column Only (not quite the same scenario I know) also still gives me the same multiple row results.

I have reached a bit if a block as to what to try next, so any suggestions most gratefully received.

score 2 · Answer 1 · answered Nov 18 '19 at 13:32

You can use aggregation, like this:

  select USERID,
         max(case when roleid = 1 then Discriminator end) as Pathologist
         max(case when roleid = 2 then Discriminator end) as Histologist
         max(case when roleid = 3 then Discriminator end) as Configuration
         max(case when roleid = 4 then Discriminator end) as Web
  FROM [Workflow].[UserRole]
  group by userid;

In an aggregation query, the group by keys specify the rows that you want returned. Every unique combination of the key(s) gets one row in the result set. You have included unnecessary keys, resulting in unnecessary rows.

SQL Server select multiple entries from a single column into a single row result

1 Answers1