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)

question from:https://stackoverflow.com/questions/65600558/sql-server-mulitple-results-to-one-row-by-id-version-2012

Answer 1

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 |
+--------+--------------------------+