Concatenate rows into string

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  July 20, 2017 at 4:00 am

  #339035

  Hi,
  my Cross-Apply returns multiple Values. How can i bring these Values to 1 String (V1,V2,V3....)  into my MainSelect?

  SELECT distinct
   a.[NAME]
  ,c.OBJEKTID   -- multiple Values-String
  FROM [Devices] a
  cross apply
  (
    Select objektid from [Devices] b
    where a.OBJEKTID = b.OBJEKTID
   ) c

  Data
  Name        OBJEKTID
  d100        1001
  d100        1002
  d100        1003
  d100        1004
  d101        1011
  d101        1012

  Result
  d100    1001,1002,1003,1004
  d101    1011,1012

  Regards
  Nicole
  :ermm:

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 20, 2017 at 4:12 am

  #1951799

  Nicole

  With no DDL or sample data, it's difficult to visualise.  But if you type "concatenate for xml" into your favourite search engine, you should get plenty of results that explain a technique to concatenate multiple rows into one.

  John

• DesNorton

  SSC-Insane

  Points: 24278

  More actions

  July 20, 2017 at 5:30 am

  #1951807

  info 58414 - Thursday, July 20, 2017 4:00 AM

  Hi,
  my Cross-Apply returns multiple Values. How can i bring these Values to 1 String (V1,V2,V3....)  into my MainSelect?

  SELECT distinct
   a.[NAME]
  ,c.OBJEKTID   -- multiple Values-String
  FROM [Devices] a
  cross apply
  (
    Select objektid from [Devices] b
    where a.OBJEKTID = b.OBJEKTID
   ) c

  Data
  Name        OBJEKTID
  d100        1001
  d100        1002
  d100        1003
  d100        1004
  d101        1011
  d101        1012

  Result
  d100    1001,1002,1003,1004
  d101    1011,1012

  Regards
  Nicole
  :ermm:

  You could try something like this
  SELECT
  a.[NAME]
, OBJEKTID = LTRIM(RTRIM(
       STUFF(
        ( SELECT ',' + CAST(b.OBJEKTID AS VARCHAR(10))
         FROM [Devices] b
         WHERE a.[NAME] = b.[NAME]
         GROUP BY b.OBJEKTID
         ORDER BY b.OBJEKTID
         FOR XML PATH(''), TYPE).value('(./text())[1]','VARCHAR(MAX)'
        ), 1, 1, '')
       ))
FROM [Devices] a
GROUP BY a.[NAME]
ORDER BY a.[NAME]

  ---

  How to post data/code on a forum to get the best help.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  July 20, 2017 at 7:30 am

  #1951829

  DesNorton - Thursday, July 20, 2017 5:29 AM

  info 58414 - Thursday, July 20, 2017 4:00 AM

  Hi,
  my Cross-Apply returns multiple Values. How can i bring these Values to 1 String (V1,V2,V3....)  into my MainSelect?

  SELECT distinct
   a.[NAME]
  ,c.OBJEKTID   -- multiple Values-String
  FROM [Devices] a
  cross apply
  (
    Select objektid from [Devices] b
    where a.OBJEKTID = b.OBJEKTID
   ) c

  Data
  Name        OBJEKTID
  d100        1001
  d100        1002
  d100        1003
  d100        1004
  d101        1011
  d101        1012

  Result
  d100    1001,1002,1003,1004
  d101    1011,1012

  Regards
  Nicole
  :ermm:

  You could try something like this
  SELECT
  a.[NAME]
, OBJEKTID = LTRIM(RTRIM(
       STUFF(
        ( SELECT ',' + CAST(b.OBJEKTID AS VARCHAR(10))
         FROM [Devices] b
         WHERE a.[NAME] = b.[NAME]
         GROUP BY b.OBJEKTID
         ORDER BY b.OBJEKTID
         FOR XML PATH(''), TYPE).value('(./text())[1]','VARCHAR(MAX)'
        ), 1, 1, '')
       ))
FROM [Devices] a
GROUP BY a.[NAME]
ORDER BY a.[NAME]

  This is good. Since the OP is concatenating numbers, however, you could lose the ", TYPE" and "value('(./text())[1]','VARCHAR(MAX)' )"

  That code prevents special XML characters such as "<>&" from messing up your result set but slows the query performance. The OP is not returning any XML characters so the performance hit is not necessary.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• DesNorton

  SSC-Insane

  Points: 24278

  More actions

  July 20, 2017 at 12:00 pm

  #1951933

  Alan.B - Thursday, July 20, 2017 7:30 AM

  This is good. Since the OP is concatenating numbers, however, you could lose the ", TYPE" and "value('(./text())[1]','VARCHAR(MAX)' )"

  That code prevents special XML characters such as "<>&" from messing up your result set but slows the query performance. The OP is not returning any XML characters so the performance hit is not necessary.

  Thanks Alan

  ---

  How to post data/code on a forum to get the best help.

• Jeff Moden

  SSC Guru

  Points: 1003861

  More actions

  July 21, 2017 at 5:28 am

  #1952040

  Alan.B - Thursday, July 20, 2017 7:30 AM

  DesNorton - Thursday, July 20, 2017 5:29 AM

  info 58414 - Thursday, July 20, 2017 4:00 AM

  Hi,
  my Cross-Apply returns multiple Values. How can i bring these Values to 1 String (V1,V2,V3....)  into my MainSelect?

  SELECT distinct
   a.[NAME]
  ,c.OBJEKTID   -- multiple Values-String
  FROM [Devices] a
  cross apply
  (
    Select objektid from [Devices] b
    where a.OBJEKTID = b.OBJEKTID
   ) c

  Data
  Name        OBJEKTID
  d100        1001
  d100        1002
  d100        1003
  d100        1004
  d101        1011
  d101        1012

  Result
  d100    1001,1002,1003,1004
  d101    1011,1012

  Regards
  Nicole
  :ermm:

  You could try something like this
  SELECT
  a.[NAME]
, OBJEKTID = LTRIM(RTRIM(
       STUFF(
        ( SELECT ',' + CAST(b.OBJEKTID AS VARCHAR(10))
         FROM [Devices] b
         WHERE a.[NAME] = b.[NAME]
         GROUP BY b.OBJEKTID
         ORDER BY b.OBJEKTID
         FOR XML PATH(''), TYPE).value('(./text())[1]','VARCHAR(MAX)'
        ), 1, 1, '')
       ))
FROM [Devices] a
GROUP BY a.[NAME]
ORDER BY a.[NAME]

  This is good. Since the OP is concatenating numbers, however, you could lose the ", TYPE" and "value('(./text())[1]','VARCHAR(MAX)' )"

  That code prevents special XML characters such as "<>&" from messing up your result set but slows the query performance. The OP is not returning any XML characters so the performance hit is not necessary.

  Heh... so does concatenation of values into a denormalized form.  I have to wonder why this particular requirement is necessary.

  --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)