Post reply

How can I make ten rows in a column into one comma seperated row

  • August 9, 2012 at 2:36 pm

    #260583

    Hi,

    I have ten rows in a one column in a table #years (year varchar(6))

    2010

    2011

    2009

    2005

    2003

    I need to change this value to a comma seperated value in one row like this

    '2010','2011','2009','2005','2003'

    Thanks

  • August 9, 2012 at 2:41 pm

    #1524125

    the trick is to use a technique using FOR XML:

    does your table have more columns than just the year? the solution's the same, but the detaisl are a little differnet.

    here's an example:

    SELECT DISTINCT

    t.name,

    sq.Columns

    FROM sys.tables t

    JOIN (

    SELECT OBJECT_ID,

    Columns = STUFF((SELECT ',' + name

    FROM sys.columns sc

    WHERE sc.object_id = s.object_id

    FOR XML PATH('')),1,1,'')

    FROM sys.columns s

    ) sq ON t.object_id = sq.object_id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 2:43 pm

    #1524126

    Only one column which is Year

  • August 9, 2012 at 4:06 pm

    #1524145

    Ajdba (8/9/2012)

    Only one column which is Year

    This help?

    DECLARE @Years TABLE (years VARCHAR(6));

    INSERT INTO @Years(years)

    VALUES ('2010'),('2011'),('2009'),('2005'),('2003');

    SELECT TheYears = STUFF((SELECT ',' + years

    FROM @Years sc

    FOR XML PATH('')),1,1,'')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply