Query help

Question

Hi All,

I have a result set as below

Name     Working Year

a              2010

a              2011

a              2012

b             2011

b             2012

Need help to get data as

a 2010,2011,2012

b 2011,2012

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 1

Try the below:

Declare @Reviewers Table(Name Varchar(50), Year varchar(50))
Insert into @Reviewers Select 'a','2010'
Insert into @Reviewers Select 'a','2011'
Insert into @Reviewers Select 'b','2012'
Select distinct Name,
           approvernames=  REPLACE( 
                        ( 
                  Select a.year as [data()]
               From @Reviewers A
            Where A.Name = b.Name
            Order by a.YEar
            FOR XML PATH ('') ), ' ', ',') 
            
            From @Reviewers B 

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

CREATE TABLE #T1
( ID INT,
  YEARS INT )
  
INSERT INTO #T1 VALUES (1,2011),(1,2012),(2,2010),(3,2013)


SELECT DISTINCT id, NAMES = REPLACE((SELECT T.YEARS FROM #T1 T WHERE T.ID = A.ID ORDER BY T.YEARS FOR XML PATH ('')),' ',',') FROM #T1 A

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 3

Declare @test Table(Name Varchar(50), WorkingYear int)
Insert into @test values('a',2010),('a',2011),('a',2012),('b',2010),('b',2011)
 
Select  Name, (STUFF((SELECT ',' + CAST(WorkingYear AS varchar(4)) FROM  @test  WHERE Name = t.Name
 FOR XML PATH('')), 1, 1, '')) AS WorkingYears
From @test t
Group by Name