none
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.

    Wednesday, February 13, 2013 8:42 AM

Answers

  • 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.

    Wednesday, February 13, 2013 8:49 AM
  • 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
    

    Wednesday, February 13, 2013 3:12 PM

All replies

  • 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.

    Wednesday, February 13, 2013 8:49 AM
  • 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


    • Edited by Kapil_KK Wednesday, February 13, 2013 9:50 AM
    • Proposed as answer by Kapil_KK Wednesday, February 13, 2013 9:51 AM
    Wednesday, February 13, 2013 9:46 AM
  • 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
    

    Wednesday, February 13, 2013 3:12 PM