SQL Server mulitple results to one row by ID (Version 2012)

Question

Consider this query and result set:

Select udbA.userId, d.dbName
from user_db_access udbA
Inner join dbList d on d.dbid = udbA.dbid 
Order By udbA.userId

1 az

1 nc_west

1 bsc_mo

1 NS_002

What I am looking for is a way to flatten this into one record. I know I can do it with a temp table and select into, but I was curious to see if a query could do it directly. A user could have up to 15 databases available to them.

Looking for results like below ( 2 columns -- userid and the database names ):

userid dbname

1 az nc_west bsc_mo NS_002

SQL Server Version: Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

How many items will you have per `userId`? Is it fixed, does it have a max or unlimited? — Charlieface, Jan 06 '21 at 17:32
I added on my answer reference on how to achieve it in older versios: https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Jorge Campos, Jan 06 '21 at 17:42

score 2 · Accepted Answer · answered Jan 06 '21 at 17:52

Assuming you want a space-delimited list of database names:

DECLARE @Access table ( userId int, dbName varchar(50) );
INSERT INTO @Access VALUES
    ( 1, 'az' ), ( 1, 'nc_west' ), ( 1, 'bsc_mo' ), ( 1, 'NS_002' );

SELECT DISTINCT
    ax.userId, db.list
FROM @Access AS ax
OUTER APPLY (

    SELECT LTRIM ( (
        SELECT ' ' + dbName AS "text()" FROM @Access AS x WHERE x.userId = ax.userId
            FOR XML PATH ( '' )
    ) ) AS list

) AS db;

Returns

+--------+--------------------------+
| userId |           list           |
+--------+--------------------------+
|      1 | az nc_west bsc_mo NS_002 |
+--------+--------------------------+

For a comma-delimited list:

SELECT DISTINCT
    ax.userId, db.list
FROM @Access AS ax
OUTER APPLY (

    SELECT STUFF ( (
        SELECT ',' + dbName AS "text()" FROM @Access AS x WHERE x.userId = ax.userId
            FOR XML PATH ( '' )
    ), 1, 1, '' ) AS list

) AS db;

Returns

+--------+--------------------------+
| userId |           list           |
+--------+--------------------------+
|      1 | az,nc_west,bsc_mo,NS_002 |
+--------+--------------------------+

Popeye · Answer 2 · 2021-01-06T17:54:14.083

You need GROUP BY and row_number as follows:

select userId,
       max(case when rn = 1 then dbName end) as val1,
       max(case when rn = 2 then dbName end) as val2,
       max(case when rn = 3 then dbName end) as val3,
       max(case when rn = 4 then dbName end) as val4
  from
(Select udbA.userId, d.dbName, 
        row_number() over (partition by udbA.userId order by d.dbName) as rn
   from user_db_access udbA
   Inner join dbList d on d.dbid = udbA.dbid ) t
group by userId

-- update

You just need two columns then use the STRING_AGG as follows:

Select udbA.userId, 
       string_agg(d.dbName, ' ') within group (order by d.dbName) as dbName 
  from user_db_access udbA
 Inner join dbList d on d.dbid = udbA.dbid
group by udbA,userId

-- For SQL server 2012

Select distinct udbA.userId, 
STUFF((SELECT distinct '' + d.dbName
         from dbList d
         where d.dbid = udbA.dbid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') AS dbid
FROM user_db_access udbA
WHERE EXISTS (SELECT 1 FROM dbList d
         where d.dbid = udbA.dbid)

Or use CTE as follows:

with CTE as
(Select udbA.userId, d.dbName
   from user_db_access udbA
   Inner join dbList d on d.dbid = udbA.dbid)
Select distinct c.userId, 
    STUFF((SELECT distinct '' + cc.dbName
             from cte cc
             where c.dbid = cc.dbid
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,0,'') AS dbid
    FROM cte c

the 2012 option did not work for me. it produces results 1 row per user per database. — Neo, Jan 06 '21 at 17:48
use Distinct as updated in the answer or use CTE as added in the answer — Popeye, Jan 06 '21 at 17:54

Jorge Campos · Answer 3 · 2021-01-06T18:48:27.107

1

Depending on your SQL Server version you can use STRING_AGG

Like this:

Select udbA.userId, string_agg(d.dbName, ', ') as name
  from user_db_access udbA
        Inner join dbList d on d.dbid = udbA.dbid 
  group by udbA.userId

If you are using an older version you already have answers here: How to make a query with group_concat in sql server

Like this:

select
    udbA.userId,
    stuff((
        select cast(',' as varchar(max)) + d.dbName
          from dbList d
         where d.dbid = udbA.dbid
         order by d.dbName
       for xml path('')), 1, 1, '') as dbList
from
    user_db_access udbA 
      inner join dbList dl on dl.dbid = udbA.dbid 
order by
    udbA.userId;

edited Jan 06 '21 at 18:48

answered Jan 06 '21 at 17:37

Jorge Campos

22,647
7
56
87

Are you able to provide an example with the query above? – Neo Jan 06 '21 at 17:49
@Neo added above – Jorge Campos Jan 06 '21 at 17:56
I am getting multiple results per user id with NULLS as some of the database names – Neo Jan 06 '21 at 18:20
Are there userId with no database associated to it ? – Jorge Campos Jan 06 '21 at 18:45
its possible yes. ( access removed versus the user ) – Neo Jan 06 '21 at 18:45
Ok, added a join on the outer query, that should prevent the nulls – Jorge Campos Jan 06 '21 at 18:48
getting this error now *Msg 208, Level 16, State 1, Line 43 Invalid object name 'dbList'.* – Neo Jan 06 '21 at 18:54
That's weird... it is just a simple join, exact the same as the original query? Essentially the error message is saying that `dbList` table does not exist to be joined at. – Jorge Campos Jan 06 '21 at 18:56
My bad I fat fingered a character, so no error. However I am getting multiple rows per user / database names versus 1 row containing all. – Neo Jan 06 '21 at 19:13
hmmm that's because of the join... changing it to an exists should work. But I will leave it as is since the accepted answer is better anyways :) – Jorge Campos Jan 07 '21 at 00:35

SQL Server mulitple results to one row by ID (Version 2012)

3 Answers3