How to do do data massage for this scenario

• koolme_85

  Ten Centuries

  Points: 1153

  More actions

  June 3, 2015 at 3:09 pm

  #398539

  i have a table in the following format

  FAFA_TypePIDFirstNameMiddleNameLastNameSplitNamesUserLogonImpersFirstNameImpersMiddleNameImpersLastNameprsn_position_descacc_id

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597050

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597051

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597052

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597053

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597054

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597055

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597056

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597057

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597058

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597059

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597060

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597061

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597062

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597063

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597064

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597065

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597066

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597067

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597068

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597069

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597070

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597071

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597072

  I am trying to get it change to the following way

  FAFA_TypePIDFirstNameMiddleNameLastNameSplitNamesUserLogonImpersFirstNameImpersMiddleNameImpersLastNameprsn_position_descacc_id

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597050,68597051,68597052....,,.68597059

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597060,68597061,68597062,68597063...,,,..68597072

  so the only column that's not duplicate is the account number as you can see above

  I am trying to get those multiple accounts into a single record by comma separation

  how can I achieve this (any example would help)

  thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  June 10, 2015 at 8:32 am

  #1804363

  Quick suggestion, use FOR XML PATH('') concatenation on the Account number column

  😎

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  June 10, 2015 at 8:38 am

  #1804366

  Have you heard about data normalization?

  I would suggest to do exactly that.

  Store data in the database table as supposed to; report/display it as you wish (comma separated list or whatever else required).

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  June 10, 2015 at 8:45 am

  #1804370

  Eugene Elutin (6/10/2015)

  ---

  Have you heard about data normalization?

  I would suggest to do exactly that.

  Store data in the database table as supposed to; report/display it as you wish (comma separated list or whatever else required).

  + 1

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• ZZartin

  SSC-Dedicated

  Points: 30894

  More actions

  June 10, 2015 at 12:11 pm

  #1804451

  You might be able to try something like this(fill in field names for your table since you didn't provide any DDL)

  CREATE TABLE #TEMP(COL_ONE varchar(30), COL_TWO varchar(30), ACCOUNT_NUM varchar(30))

  INSERT INTO #TEMP

  SELECT 'aaaa', 'bbbbb', 'ONE'

  UNION ALL

  SELECT 'aaaa', 'bbbbb', 'TWO'

  UNION ALL

  SELECT 'aaaa', 'bbbbb', 'THREE'

  UNION ALL

  SELECT 'bye', 'again', 'FOUR'

  UNION ALL

  SELECT 'bye', 'again', 'FIVE'

  SELECT * FROM #TEMP

  SELECT COL_ONE, COL_TWO, STUFF(ACCOUNTS.COL, LEN(ACCOUNTS.COL), 1, '') FROM #TEMP TEMP_ONE

  CROSS APPLY

  (SELECT (SELECT ACCOUNT_NUM + ',' FROM #TEMP TEMP_TWO WHERE TEMP_ONE.COL_ONE = TEMP_TWO.COL_ONE AND TEMP_ONE.COL_TWO = TEMP_TWO.COL_TWO FOR XML PATH('')) AS COL) ACCOUNTS

  GROUP BY COL_ONE, COL_TWO, ACCOUNTS.COL

• JManuelN

  SSCommitted

  Points: 1708

  More actions

  June 11, 2015 at 3:00 am

  #1804619

  I think that you could try to use the PIVOT operator too. Other option could also be an scalar function that receives a primary key an then returns a string with concatenated fields.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 15, 2015 at 9:00 am

  #1805477

  koolme_85 (6/3/2015)

  ---

  i have a table in the following format

  FAFA_TypePIDFirstNameMiddleNameLastNameSplitNamesUserLogonImpersFirstNameImpersMiddleNameImpersLastNameprsn_position_descacc_id

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597050

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597051

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597052

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597053

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597054

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597055

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597056

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597057

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597058

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597059

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597060

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597061

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597062

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597063

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597064

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597065

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597066

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597067

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597068

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597069

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597070

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597071

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597072

  I am trying to get it change to the following way

  FAFA_TypePIDFirstNameMiddleNameLastNameSplitNamesUserLogonImpersFirstNameImpersMiddleNameImpersLastNameprsn_position_descacc_id

  82P5 pri56785fnmem nameL nameJeanette RaylesJRAYLESJeanetteLeaRaylesBranch Manager68597050,68597051,68597052....,,.68597059

  1W53 spl32220BasilBasilHarrisBRUCE HARRISCHARRISBasilBasilHarrisBranch Manager68597060,68597061,68597062,68597063...,,,..68597072

  so the only column that's not duplicate is the account number as you can see above

  I am trying to get those multiple accounts into a single record by comma separation

  how can I achieve this (any example would help)

  thanks

  You can definitely use FOR XML PATH(''), as follows:

  WITH TEST_DATA AS (

  SELECT '82P5' AS FA, 'pri' AS FA_Type, 56785 AS PID, 'fnme' AS FirstName, 'm name' AS MiddleName,'L name' AS LastName, 'Jeanette Rayles' AS SplitNames,

  'JRAYLES' AS UserLogon, 'Jeanette' AS ImpersFirstName, 'Lea' AS ImpersMiddleName, 'Rayles' AS ImpersLastName, 'Branch Manager' AS prsn_position_desc,

  68597050 AS acc_id UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597051 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597052 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597053 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597054 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597055 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597056 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597057 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597058 UNION ALL

  SELECT '82P5','pri',56785,'fnme','m name','L name','Jeanette Rayles','JRAYLES','Jeanette','Lea','Rayles','Branch Manager',68597059 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597060 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597061 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597062 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597063 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597064 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597065 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597066 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597067 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597068 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597069 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597070 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597071 UNION ALL

  SELECT '1W53','spl',32220,'Basil','Basil','Harris','BRUCE HARRIS','CHARRIS','Basil','Basil','Harris','Branch Manager',68597072

  ),

  GROUPED_DATA AS (

  SELECT T.FA, T.FA_Type, T.PID, T.FirstName, T.MiddleName, T.LastName, T.SplitNames, T.UserLogon,

  T.ImpersFirstName, T.ImpersMiddleName, T.ImpersLastName, T.prsn_position_desc

  FROM TEST_DATA AS T

  GROUP BY T.FA, T.FA_Type, T.PID, T.FirstName, T.MiddleName, T.LastName, T.SplitNames, T.UserLogon,

  T.ImpersFirstName, T.ImpersMiddleName, T.ImpersLastName, T.prsn_position_desc

  )

  SELECT G.*,

  STUFF(

  (SELECT ',' + CAST(T.acc_id AS varchar(9)) AS [text()]

  --Add a comma (,) before each value

  FROM TEST_DATA AS T

  WHERE T.FA = G.FA

  FOR XML PATH('') -- Select it as XML

  )

  , 1, 1, '' ) AS ACC_ID_LIST

  -- Remove that first comma via STUFF

  FROM GROUPED_DATA AS G

  You may need to adjust the GROUPED_DATA CTE's GROUP BY clause to use the proper grouping level, but this should give you what you're looking for. You can find some more info on it's usage here:

  http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/[/url]

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)