How to do do data massage for this scenario

  • 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

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

    😎

  • 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]

  • 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]

  • 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

  • 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.

  • 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]

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply