SQL Server select multiple entries from a single column into a single row result : <p>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.</p>

<p>So my userrole data table looks like this,</p>

<pre><code>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
</code></pre>

<p>and my target result is </p>

<pre><code>3483    Pathologist Histotech   Configuration   WebViewer
3484    Pathologist                             WebViewer
3484    Pathologist                             WebViewer
3487    Pathologist Histotech   Configuration   WebViewer
</code></pre>

<p>etc.</p>

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

<pre><code>  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
</code></pre>

<p>gives </p>

<pre><code>    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
</code></pre>

<p>Trying to use a DISTINCT or MIN function on the userid as suggested in <a href="https://stackoverflow.com/questions/11937206/sql-query-multiple-columns-using-distinct-on-one-column-only">SQL Query Multiple Columns Using Distinct on One Column Only</a> (not quite the same scenario I know) also still gives me the same multiple row results.</p>

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