June 3, 2015 at 3:09 pm
i have a table in the following format
FA FA_Type PID FirstName MiddleName LastName SplitNames UserLogon ImpersFirstName ImpersMiddleName ImpersLastName prsn_position_desc acc_id
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597050
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597051
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597052
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597053
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597054
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597055
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597056
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597057
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597058
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597059
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597060
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597061
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597062
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597063
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597064
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597065
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597066
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597067
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597068
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597069
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597070
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597071
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597072
I am trying to get it change to the following way
FA FA_Type PID FirstName MiddleName LastName SplitNames UserLogon ImpersFirstName ImpersMiddleName ImpersLastName prsn_position_desc acc_id
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597050,68597051,68597052....,,.68597059
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597060,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
June 10, 2015 at 8:32 am
Quick suggestion, use FOR XML PATH('') concatenation on the Account number column
😎
June 10, 2015 at 8:38 am
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).
June 10, 2015 at 8:45 am
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
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]
June 10, 2015 at 12:11 pm
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
June 11, 2015 at 3:00 am
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.
June 15, 2015 at 9:00 am
koolme_85 (6/3/2015)
i have a table in the following formatFA FA_Type PID FirstName MiddleName LastName SplitNames UserLogon ImpersFirstName ImpersMiddleName ImpersLastName prsn_position_desc acc_id
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597050
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597051
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597052
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597053
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597054
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597055
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597056
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597057
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597058
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597059
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597060
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597061
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597062
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597063
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597064
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597065
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597066
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597067
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597068
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597069
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597070
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597071
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597072
I am trying to get it change to the following way
FA FA_Type PID FirstName MiddleName LastName SplitNames UserLogon ImpersFirstName ImpersMiddleName ImpersLastName prsn_position_desc acc_id
82P5 pri 56785 fnme m name L name Jeanette Rayles JRAYLES Jeanette Lea Rayles Branch Manager 68597050,68597051,68597052....,,.68597059
1W53 spl 32220 Basil Basil Harris BRUCE HARRIS CHARRIS Basil Basil Harris Branch Manager 68597060,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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply