Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Concatenation Isssue (SELECT QUERY)

    here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.


    XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
    XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
    XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    something like SUM() for varchar data type.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    avast, you should be doing this in your application layer, me hearty

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.

    -- Prepare sample data
    DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

    INSERT @Sample
    SELECT 290780, 'LT' UNION ALL
    SELECT 290780, 'AY' UNION ALL
    SELECT 290781, 'ILS' UNION ALL
    SELECT 290780, 'AY'

    SELECT * FROM @Sample

    -- Show the expected output
    SELECT DISTINCT s1.ID,
    STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
    FROM @Sample AS s1
    ORDER BY s1.ID

    SELECT DISTINCT s1.ID,
    STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
    FROM @Sample AS s1
    ORDER BY s1.ID

    SELECT DISTINCT s1.ID,
    STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
    FROM @Sample AS s1
    ORDER BY s1.ID

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    but my database is 2000. not 2005. and in 2000 the above code will not work.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Why not do it in the presentation layer/

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This here DBA needs to walk the plank.
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Arggggghhhh

    shiver me timbers
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your wood is cold..?
    I just don't even want to know



    ...seriously, what doesthat phrase even mean?
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    ...seriously, what doesthat phrase even mean?
    haaaarr, ye be a pitiful excuse for a young pirate, me lad

    http://en.wikipedia.org/wiki/Shiver_my_timbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    hahaha its true its Pirate day today aaaaaarrrrrrr

  12. #12
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Brett Kaiser
    Arggggghhhh

    shiver me timbers

    Brett, Opie and Anthony fan ?

    They had pirate talk today.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    buffett

    He mentioned it last night at his concert at MSG
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Brett Kaiser
    buffett

    He mentioned it last night at his concert at MSG
    Rush Fan ? they played Monday night at MSG.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    Rush Fan ? they played Monday night at MSG.

    In another lifetime
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •