How to use stuff function in this case

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  February 20, 2015 at 7:45 pm

  #1779093

  In case anyone is interested, I've corrected the previously faulty code as follows. I also tried to make it a little bit more readable.

  SELECT a, b, stuff(

  (

  SELECT ', ' + CAST(level1 AS VARCHAR(8000))

  FROM (

  SELECT a, b, c + '(' + stuff(

  (

  SELECT ', ' + CAST(d AS VARCHAR(8000))

  FROM #example

  WHERE a = x.a

  AND b = x.b

  AND c = x.c

  FOR XML path('')

  ) + ')'

  , 1, 1, '') AS level1

  FROM #example AS x

  GROUP BY a, b, c

  ) y

  WHERE a = z.a

  AND b = z.b

  FOR XML path('')

  )

  , 1, 1, '') AS level2

  FROM (

  SELECT a, b

  FROM #example

  GROUP BY a, b

  ) z

  ;

  The only reason why I can see to avoid a CTE is if the real goal is to make it run in something like MySQL. So much for the myth of portability. 😉

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