Post reply

select comma Separated values

  • January 21, 2010 at 4:25 am

    #73477

    Hi,

    I have one temp table with two columns, contain the values like..

    Id Name

    -------------

    1 A

    1 A1

    1 A2

    2 B

    2 B1

    Now I want to show the results as...

    Id Name

    --------------

    1 A,A1,A2

    2 B,B1

    Can anyone give me the solution for this one.

    Regards,

    Ram

  • January 21, 2010 at 4:28 am

    #1106592

    Try this....

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    Please confirm which SQL Version you are running.


    Clear Sky SQL
    My Blog[/url]

  • January 21, 2010 at 4:36 am

    #1106595

    Apologies Wrong link

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/


    Clear Sky SQL
    My Blog[/url]

  • January 21, 2010 at 4:53 am

    #1106602

    Hi,

    Thanks for youy replay. I am using sql server 2000.

  • January 21, 2010 at 7:03 am

    #1106670

    here is a sql 2000 compatible example; this is updating a third column in the table to contain the concatenated values;

    you might be able to adapt this example to your situation:

    ------------DDL----------------

    create table test (id int identity, category varchar(100),

    name varchar(100), allnames varchar(8000) null)

    insert test (category, name)

    select 'fruit', 'apple' union

    select 'fruit', 'pear' union

    select 'fruit', 'orange' union

    select 'meat' , 'beef' union

    select 'meat' , 'pork' union

    select 'meat' , 'bacon'

    ------------------------------------

    declare @category varchar(20), @name varchar(20)

    --select @name ='' ,@category =category from test where id = 1

    update t

    set @name = case when @category = category then @name +','+name else name end , allnames = @name, @category = category

    from test t

    update test

    set allnames = x.allnames

    from test t

    join (select max(allnames)allnames, category from test group by category)x

    on x.category = t.category

    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!

  • January 21, 2010 at 11:35 pm

    #1107115

    Here are a couple of more suggestions along with some caveats to watch out for.

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 3, 2010 at 4:00 am

    #1112734

    Use of coalesce will help this

  • February 3, 2010 at 5:41 am

    #1112778

    heartbeat_yuva (2/3/2010)

    Use of coalesce will help this

    Actually it won't. Other than a 2 part coalesce acting in mostly the same fashion as an ISNULL, it's a little bit slower than ISNULL and suffers all the same caveats of usage.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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