Comma separated list of distinct values

• PSB

  SSChampion

  Points: 13195

  More actions

  October 14, 2015 at 8:50 am

  #301495

  Hi,

  I am trying to create a comma delimited list of InvNo along with the JobNo .

  CREATE TABLE #ListString

  (

  JobNo VARCHAR(10),

  InvNo VARCHAR(MAX)

  )

  INSERT INTO #ListString ( JobNo, InvNo )

  SELECT '3079', 'abc'

  UNION ALL

  SELECT '3079', 'def'

  UNION ALL

  SELECT '3079', 'ghi'

  UNION ALL

  SELECT '3079', 'jkl'

  UNION ALL

  SELECT '1099', '123a'

  UNION ALL

  SELECT '3000', 'hhi'

  SELECT * FROM #ListString

  --DESIRED result

  SELECT '3079' AS JobNo, 'abc,def,ghi,jkl' AS InvNo UNION ALL

  SELECT '1099'JobNo, '123a' AS InvNo UNION ALL

  SELECT '3000'JobNo, 'hhi' AS InvNo

  DROP TABLE #ListString

  Please help .

  Thanks,

  PSB

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  October 14, 2015 at 8:58 am

  #1833269

  Use the FOR XML PATH technique described here.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• PSB

  SSChampion

  Points: 13195

  More actions

  October 14, 2015 at 9:28 am

  #1833281

  I am not getting distinct values .

  CREATE TABLE #ListString

  (

  JobNo VARCHAR(10),

  InvNo VARCHAR(MAX),

  Address1 VARCHAR(100)

  )

  INSERT INTO #ListString ( JobNo, InvNo,Address1 )

  SELECT '3079', 'abc' ,'Add1'

  UNION ALL

  SELECT '3079', 'abc' ,'Add2'-----repeat

  UNION ALL

  SELECT '3079', 'def' ,'Add1'

  UNION ALL

  SELECT '3079', 'ghi' ,'Add1'

  UNION ALL

  SELECT '3079', 'jkl' ,'Add1'

  UNION ALL

  SELECT '1099', '123a' ,'Add1'

  UNION ALL

  SELECT '3000', 'hhi' ,'Add1'

  SELECT * FROM #ListString

  --DESIRED result has abc,abc repeated for 3079

  SELECT '3079' AS JobNo, 'abc,abc,def,ghi,jkl' AS InvNo UNION ALL

  SELECT '1099'JobNo, '123a' AS InvNo UNION ALL

  SELECT '3000'JobNo, 'hhi' AS InvNo

  DROP TABLE #ListString

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  October 14, 2015 at 9:40 am

  #1833287

  PSB (10/14/2015)

  ---

  I am not getting distinct values .

  CREATE TABLE #ListString

  (

  JobNo VARCHAR(10),

  InvNo VARCHAR(MAX),

  Address1 VARCHAR(100)

  )

  INSERT INTO #ListString ( JobNo, InvNo,Address1 )

  SELECT '3079', 'abc' ,'Add1'

  UNION ALL

  SELECT '3079', 'abc' ,'Add2'-----repeat

  UNION ALL

  SELECT '3079', 'def' ,'Add1'

  UNION ALL

  SELECT '3079', 'ghi' ,'Add1'

  UNION ALL

  SELECT '3079', 'jkl' ,'Add1'

  UNION ALL

  SELECT '1099', '123a' ,'Add1'

  UNION ALL

  SELECT '3000', 'hhi' ,'Add1'

  SELECT * FROM #ListString

  --DESIRED result has abc,abc repeated for 3079

  SELECT '3079' AS JobNo, 'abc,abc,def,ghi,jkl' AS InvNo UNION ALL

  SELECT '1099'JobNo, '123a' AS InvNo UNION ALL

  SELECT '3000'JobNo, 'hhi' AS InvNo

  DROP TABLE #ListString

  Well thanks for repeating your original post verbatim, that really helps a lot.

  Now please post the FOR XML PATH version you have tried.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• PSB

  SSChampion

  Points: 13195

  More actions

  October 14, 2015 at 9:43 am

  #1833288

  Here you go ...

  CREATE TABLE #ListString

  (

  JobNo VARCHAR(10),

  InvNo VARCHAR(MAX),

  Address1 VARCHAR(100)

  )

  INSERT INTO #ListString ( JobNo, InvNo ,Address1)

  SELECT '3079', 'abc' ,'Add1'

  UNION ALL

  SELECT '3079', 'abc' ,'Add2'

  UNION ALL

  SELECT '3079', 'def' ,'Add1'

  UNION ALL

  SELECT '3079', 'ghi' ,'Add1'

  UNION ALL

  SELECT '3079', 'jkl' ,'Add1'

  UNION ALL

  SELECT '1099', '123a' ,'Add1'

  UNION ALL

  SELECT '3000', 'hhi' ,'Add1'

  SELECT * FROM #ListString

  --DESIRED result

  SELECT DISTINCT

  P.JobNo,

  STUFF

  (

  (

  SELECT ',' + InvNo

  FROM #ListString M

  WHERE M.JobNo = P.JobNo

  ORDER BY InvNo

  FOR XML PATH('')

  ), 1, 1, ''

  ) AS Models

  FROM

  #ListString P

  DROP TABLE #ListString

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 14, 2015 at 9:46 am

  #1833289

  Why would you expect to have distinct values if you're not telling the engine to return distinct values?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  October 14, 2015 at 9:51 am

  #1833293

  Modify the FOR XML subsquery to contain a DISTINCT clause on the ','+InvNo expression, which also requires adding the same expression to the ORDER BY clause.

  SELECT DISTINCT ',' + InvNo

  FROM #ListString M

  WHERE M.JobNo = P.JobNo

  ORDER BY ',' + InvNo

  FOR XML PATH('')

  The results appear to contain distinct values as you request.

  1099123a

  3000hhi

  3079abc,def,ghi,jkl

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho