Add multiple varchar columns (subquery/loop?)

Question

I have 3 tables and I'm trying to create a report...

Table MP (data table)
mID col1    col2
1   data1   data2
2   data3   data4
3   data5   data6

Table MPU (user table)
uID UserName
1   user1
2   user2
3   user3

Table MPR (lookup table)
rID uID mID
1   1   2
2   2   2
3   1   1

This is the basic select logic I need:

Select
    MP.mID
    ,MP.col1
    ,MP.col2
    ,MPU.UserName --format as 'user1, user2, user3' ...
From MP
left join MPR on MPR.mID  = MP.mID
left join MPU on MPU.uID = MPR.uID

I need to add the username strings together as commented in code.
Some data rows will have no users and others could have as many as 10.
I don't want multiple rows being populated as it is now.

EDIT:
Note - SQL 2000

You might want to try COALLESCE.. that would bring together all non null usernames... I will have to look up the format and post it later — logixologist, Jun 12 '13 at 22:18
There are various solutions like [this](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) for creating comma delimited lists. FYI: Your sample `MP` table doesn't have an `rID` column, but your query uses it. — HABO, Jun 13 '13 at 00:06
That solution only works for 2005 and newer. I'm so fortunate enough to be working in 2000. -also fixed join. — McG369, Jun 13 '13 at 06:39

score 0 · Answer 1 · answered Jun 19 '13 at 14:23

I can suggest only this approach for MSSQL 2000:

select
    mID,
    col1,
    col2,
    MAX( CASE seq WHEN 1 THEN UserName ELSE '' END )  +
    MAX( CASE seq WHEN 2 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 3 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 4 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 5 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 6 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 7 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 8 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 9 THEN ', '+UserName ELSE '' END ) + 
    MAX( CASE seq WHEN 10 THEN ', '+UserName ELSE '' END ) 

FROM (
    Select
        MP1.mID,
        MP1.col1,
        MP1.col2,
        MPU1.UserName,
        (
            SELECT COUNT(*)
            From MP as MP2
            left join MPR as MPR2 on MPR2.mID  = MP2.mID
            left join MPU as MPU2 on MPU2.uID = MPR2.uID
            WHERE MP2.mID = MP1.mID
            AND MPU2.UserName <= MPU1.UserName
        ) seq
    From MP as MP1
    left join MPR as MPR1 on MPR1.mID  = MP1.mID
    left join MPU as MPU1 on MPU1.uID = MPR1.uID
) t1
GROUP BY mID, col1, col2;

Add multiple varchar columns (subquery/loop?)

1 Answers1