Comma Separated SQL Server Result Set 'JOINED' with other Columns

Question

I have a table say ProjectMaster:

  Id       ProjectName
  1         A
  2         B
  3         C

another table ProjectMeter

 Id   ProjectId   MeterNumber
 1      1          #0001
 2      1          #0002
 3      1          #0003 
 4      2          #0004
 5      2          #0005 
 6      3          #0006

I wish to have following output

ProjectName   MeterNumbers 
 A             #0001, #0002, #0003
 B             #0004, #0005
 C             #0006

I tried this and this, but unable to solve my problem. I cannot use a table variable.

I have a already written Stored Procedure and it brings data from many joined tables. ProjectMaster also happens to be joined in one of these tables. Now am required to fetch data from ProjectMeter, such that, each row has concatenated ProjectMeter.MeterNumber corresponding to the ProjectId in that column.

right now, I get concatenated list of all meternumbers in all the rows.

I cannot use CURSOR, TABLE variable , Temp TABLE

( I hope still something can be done to my cause)

please help.....

I think that if you were using MySQL, you can solve that with Group_Concat. So, have you tried this ? http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Gonzalo.-, Aug 29 '12 at 13:51
Similar question already on [StackOverflow][1]. [1]: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Krešimir Lukin, Aug 29 '12 at 13:54

Taryn · Accepted Answer · 2012-11-20T10:27:50.047

4

Try this:

SELECT projectname, STUFF((SELECT distinct ', ' + meternumber 
              from projectmeter m
              where p.id = m.projectid
            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') MeterNumbers
from projectmaster p

See SQL Fiddle with Demo

edited Nov 20 '12 at 10:27

answered Aug 29 '12 at 13:57

Taryn

242,637
56
362
405

score 1 · Answer 2 · answered Aug 29 '12 at 13:58

DECLARE @ProjectMaster AS TABLE
    (
      ID INT IDENTITY(1, 1) ,
      ProjectName VARCHAR(2)
    )
DECLARE @ProjectMeter AS TABLE
    (
      ID INT IDENTITY(1, 1) ,
      ProjectID INT ,
      MeterNumber VARCHAR(50)
    )

INSERT  INTO @ProjectMaster
        ( ProjectName )
VALUES  ( 'A' )

INSERT  INTO @ProjectMeter
        ( ProjectID, MeterNumber )
VALUES  ( 1, '#0001' )
INSERT  INTO @ProjectMeter
        ( ProjectID, MeterNumber )
VALUES  ( 1, '#0002' )

SELECT pMaster.ID, STUFF(( SELECT  ',' + MeterNumber
                FROM    @ProjectMeter
              FOR
                XML PATH('')
              ), 1, 1, '') AS 'Concat Result'
FROM    @ProjectMeter pMeter
        INNER JOIN @ProjectMaster pMaster ON pMaster.ID = pMeter.ProjectID
GROUP BY pMaster.ID

I have used table variables here but surely you just need to drop the @'s as I have used the same table names as you have specified? Not sure if this is okay? :)

no @LukeHennerley..it is not working..thnx for response anyways — Manish Mishra, Aug 29 '12 at 14:02

score 1 · Answer 3 · answered Aug 29 '12 at 15:02

Also in MS SQL you can do it using recursive query with CTE.

Here is a SQLFiddle demo

;with t1 as (
 select t.*,
        cast(meternumber as varchar(max)) as m2,
        0 as level
     from ProjectMeter t 
     where not exists 
        (select id 
                from ProjectMeter l 
          where l.id<t.id and l.ProjectId=t.ProjectID 
        )
 union all
 select b.*,
        cast(c.m2+','+b.MeterNumber as varchar(max)) as m2,
        c.level+1 as level
     from ProjectMeter b
         inner join t1 c
             on (c.id < b.id) and (b.ProjectID=c.ProjectId)

)

select pm.ProjectName as ProjectName, 
       t1.m2 as MeterNumbers 
      from t1 
      inner join
        (select ProjectId,max(level) ml 
              from t1 
          group by ProjectId
        ) t2  
            on (t1.ProjectId=t2.ProjectID) and (t1.level=t2.ml)
       left join ProjectMaster pm 
           on (t1.ProjectId=pm.Id)
order by t1.ProjectID

Comma Separated SQL Server Result Set 'JOINED' with other Columns

3 Answers3