getting concatenated data with a self join

• pgreene-761712

  Newbie

  Points: 1

  More actions

  November 5, 2008 at 11:53 am

  #200936

  I've got a table that I'm trying to query with a self join that I must not be doing correctly. Any help would be greatly appreciated. The table looks something like below:

  A B C

  1 x 1

  1 y 1

  2 z 1

  3 a 2

  3 b null

  3 c 2

  4 d null

  what I'm trying to pull out is for all unique column A values, a second column with the values in B concatenated if C is not null, so the result set for the above data would be:

  1 xy

  2 z

  3 ac

  hopefully that makes sense, and thanks in advance for your help!

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  November 5, 2008 at 1:48 pm

  #894514

  You should provide sample code for next time

  but after some scratching my head, I have to resolve to XML method (can be used in SQL2005 so should work for you)

  Customize to your situation

  -- Jerry Hung

  SET XACT_ABORT, NOCOUNT ON

  DECLARE @test TABLE (

  A VARCHAR(10),

  B VARCHAR(10),

  C VARCHAR(10)

  )

  INSERT INTO @test

  SELECT'1','x','1'

  UNION ALL

  SELECT'1','y','1'

  UNION ALL

  SELECT'2','z','1'

  UNION ALL

  SELECT'3','a','2'

  UNION ALL

  SELECT'3','b',NULL

  UNION ALL

  SELECT'3','c','2'

  UNION ALL

  SELECT'4','d',NULL

  --SELECT * FROM @test

  /*

  A B C

  1 x 1

  1 y 1

  2 z 1

  3 a 2

  3 b null

  3 c 2

  4 d null

  */

  SELECT

  T1.A

  ,Test=

  REPLACE((

  SELECT B [data()] FROM @test T3

  WHERE T3.A=T1.A

  AND T3.C IS NOT NULL

  FOR XML PATH('')

  ), ' ', '')

  FROM @test T1

  WHERE T1.C IS NOT NULL

  GROUP BY T1.A

  /*

  1 xy

  2 z

  3 ac

  */

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• Jeff Moden

  SSC Guru

  Points: 1004650

  More actions

  November 5, 2008 at 8:07 pm

  #894637

  ... and please see the following article for some potential problems you can avoid with some other methods of concatenation...

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

  Jerry's method does not have such problems.

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