-2

This is what i am trying to do, i want to group with same component id but can apply group by as this is the column in the view.

SELECT Business_Analyst_Team = STUFF((
                                      SELECT DISTINCT ', ' + A.ResourceName
                                        FROM  (
                                                Select ResourceName 
                                                      ,Component_ID
                                                 FROM [WFS].[Component_Resource] 
                                                 join  dbo.MSP_EPMResource_UserView 
                                                 on dbo.MSP_EPMResource_UserView.ResourceEmailAddress = [WFS].[Component_Resource].User_Email
                                              where [Role] ='Team Member' 
                                                and Functional_Group ='Product') A
                                               where A.Component_ID = [WFS].[Component_Resource].Component_ID
                                                 FOR XML PATH('')), 1, 2, '')
                                                FROM [WFS].[Component_Resource]

This is what i am getting , i wanted names with same component_id should come in same column Business_Analyst_Team ** **Component_ID

A                             1
b                           28439
c                           28439
d                           28439
e                           28439

Wanted like this

**Business_Analyst_Team **
A
b,c,d,e

GeekyNerd
  • 1
  • 5
  • 1
    Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Thom A Sep 03 '18 at 12:19
  • @Larnu i dont want to use group by at end as this is column in view and view contains so many columns,without group by at end i need to achieve this, may be we can use in between – GeekyNerd Sep 03 '18 at 12:21
  • How are you expecting to "aggregate" the rows then? `DISTINCT`? Your comment conflicts with your question too, which states *"but can apply group by"*. Can and can't are complete opposites. Which is it? – Thom A Sep 03 '18 at 12:26
  • can you please help – GeekyNerd Sep 03 '18 at 14:34
  • What's wrong the the solution in the link I've given? – Thom A Sep 03 '18 at 14:37
  • i cant use group by and this query i have to use in a view – GeekyNerd Sep 03 '18 at 14:58
  • Again, that's not what your question says... *"i want to group with same component id but **can** apply group by as this is the column in the view"*. Can and can't aren't the same thing. Which is it? Update your question if you can't. If you can, why are you now saying you can't? What's wrong with the answer below? You haven't specified what's wrong with it? The sample data we have only has 2 columns, so I can't see any good reason why you can't use `GROUP BY Component_ID`. – Thom A Sep 03 '18 at 15:01

1 Answers1

0

use sub-query

select distinct * from 
(SELECT


    stuff(
    (
    select cast(',' as varchar(max)) + U.team
    from t U
    WHERE U.id = G.id
    order by U.team
    for xml path('')
    ), 1, 1, '') AS concat
FROM
    t G

    ) as a

http://sqlfiddle.com/#!18/d17b4/4

concat
A
b,c,d,e
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63