0

Here is the sample data I have in table T1

CREATE TABLE T1 (TRANS_ID INT , SCENARIO VARCHAR(50))
INSERT INTO T1 VALUES (1,'DA_HRNM'),(1,'DA_HRNM'),(2,'DA_KW'),(2,'DA_HRC'),(3,'DA_BOG'),(3,'DA_HRC'),(3,'DA_HRC')

I want to write a query to return following output -

Expected Output

Basically I want DISTINCT comma separated values in col SCENARIO GROUP BY TRANS_ID

Reeya Oberoi
  • 813
  • 5
  • 19
  • 42
  • Google: "sql server aggregate string concatenation". – Gordon Linoff Sep 09 '15 at 02:19
  • 1
    possible duplicate of [concatenating values from string column in aggregate query in sql server](http://stackoverflow.com/questions/5473947/concatenating-values-from-string-column-in-aggregate-query-in-sql-server) – C8H10N4O2 Sep 09 '15 at 02:23
  • 1
    possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) – Felix Pamittan Sep 09 '15 at 02:27
  • Thank you @FelixPamittan and others for posting the URLs. I will go through it, as of now, I feel I may be able to get csv values after reading up on the links but how will I get DISTINCT csv ? – Reeya Oberoi Sep 09 '15 at 02:30

1 Answers1

0

You neeed distinct and for xml path

sql fiddle demo

WITH onlyone as (
      SELECT DISTINCT TRANS_ID, SCENARIO
      FROM T1 T
)    
SELECT  DISTINCT p.TRANS_ID, 
        STUFF(( SELECT ',' + CAST(T.SCENARIO as varchar(10) )
                FROM onlyone T
                WHERE T.TRANS_ID = p.TRANS_ID
                ORDER BY T.SCENARIO
                FOR
                   XML PATH('')
                ), 1, 1, '') AS SCENARIO
FROM onlyone p
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118