How to phrase a T-SQL horizontal join?

I have a database which is including two tables, Labs and LabUsers How do I join a column from LabUsers into a cell in Labs.

To be specific, I would like to have the user names of a lab to be concatenated and separated with a bullet ( • is Alt+0149), and to have these usernames sorted alphabetically left to right.

Here is an example.

The Labs table looks like this:

LabID  LabName     LabStudents
-----  ----------  -----------
1      North       NULL
2      North East  NULL
3      South West  NULL

and the LabUsers looks like this:

LabUserID   LabUserName   LabID
---------   -----------   -----    
1           Diana         1
2           Paul          2
3           Paula         2
4           Romeo         1
5           Julia         1
6           Rose          2
7           Diana         2

I would like to get this outcome in the Labs table:

LabID  LabName     LabUsers
-----  ----------  ---------------------
1      North       Diana•Julia•Romeo
2      North East  Diana•Paul•Paula•Rose
3      South West  NULL

Here is the script to create the tables:

USE [tempdb];
GO
CREATE TABLE [dbo].[LabUsers]
(
    [LabUserID] [int] PRIMARY KEY CLUSTERED,
    [LabUserName] [nvarchar](50) NOT NULL,
    [LabID] [int] NOT NULL
);
GO
INSERT [dbo].[LabUsers] SELECT 1, N'Diana', 1;
INSERT [dbo].[LabUsers] SELECT 2, N'Paul',  2;
INSERT [dbo].[LabUsers] SELECT 3, N'Paula', 2;
INSERT [dbo].[LabUsers] SELECT 4, N'Romeo', 1;
INSERT [dbo].[LabUsers] SELECT 5, N'Julia', 1;
INSERT [dbo].[LabUsers] SELECT 6, N'Rose',  2;
INSERT [dbo].[LabUsers] SELECT 7, N'Diana', 2;

CREATE TABLE [dbo].[Labs]
(
    [LabID] [int] PRIMARY KEY CLUSTERED,
    [LabName] [nvarchar](50) NOT NULL,
    [LabUsers] [nvarchar](max) NULL
);
GO
INSERT [dbo].[Labs] SELECT 1, N'North',      NULL;
INSERT [dbo].[Labs] SELECT 2, N'North East', NULL;
INSERT [dbo].[Labs] SELECT 3, N'South West', NULL;

Solution 1:

SELECT l.LabID, l.LabName, LabUsers = STUFF((SELECT N'•' + lu.LabUserName
  FROM dbo.LabUsers AS lu 
  WHERE lu.LabID = l.LabID
  ORDER BY lu.LabUserName
  FOR XML PATH(''), 
  TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
FROM dbo.Labs AS l;

I see absolutely no reason to store this in the table, since you can always generate the code at runtime when you run a query. If you store it in the table, then you have to update it every single time you change any row in the table.

However, if I can't convince you not to do this (it really is bad to store redundant data like this), you can try this way:

;WITH x AS
(
    SELECT l.LabID, l.LabName, x = STUFF((SELECT N'•' + lu.LabUserName
      FROM dbo.LabUsers AS lu 
      WHERE lu.LabID = l.LabID
      ORDER BY lu.LabUserName
      FOR XML PATH(''), 
      TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
    FROM dbo.Labs AS l
)
UPDATE l
SET LabUsers = x.x
FROM dbo.Labs AS l
INNER JOIN x ON l.LabID = x.LabID;

As for the performance tests, I'd compare the above version with this variation:

SELECT l.LabID, l.LabName, LabUsers = STUFF((SELECT N'•' + lu.LabUserName
  FROM dbo.LabUsers AS lu 
  WHERE lu.LabID = l.LabID
  ORDER BY lu.LabUserName
  FOR XML PATH('')), 1, 1, '')
FROM dbo.Labs AS l;

On my system I see the initial version at the top of this answer to be far more expensive. Also note that stuffing (no pun intended) these approaches into a user-defined function will bring it closer to the concatenation method @RThomas proposed.

Solution 2:

Give this a try

SELECT  LabName ,
        STUFF(( SELECT  ',' + LabUsers.LabUserName
                FROM    dbo.LabUsers
                WHERE   LabUsers.LabID = Labs.LabID
                ORDER BY LabName
              FOR
                XML PATH('')
              ), 1, 1, '') AS Labusers
FROM    dbo.Labs
ORDER BY LabName

The FOR XML PATH('') concatenates your strings together into one XML result and the STUFF puts a "nothing" character at the first character, e.g. wipes out the unneeded first comma.