Link to home
Start Free TrialLog in
Avatar of newknew
newknew

asked on

SQL to transform records into a string value

Table: "loads_commissions" Relevant Fields: "LoadID", "CommissionTeam"

Objective: A comma delimited string column that represents an unknown number of records for each  "CommissionTeam" record per "LoadID".  In all cases there will be less than 10 loads_commissions records for each LoadID.  There are 30,000+ unique LoadIDs

For Example:
LoadID   |    CommissionTeam
1                            4
1                            5
2                            4
2                            2

Output:
LoadID      |    CommissionString
1                          4,5
2                          4,2

Why:  String is needed for export to unrelated  accounting software to represent all persons to be paid per that load.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you do not have SQL 2017+ and data can be converted to XML without consequence as in your example, you can use FOR XML.

SELECT id.LoadID
     /* STUFF removes extra comma in CSV */
     , CommissionTeamList = STUFF(ct.CommissionTeamList, 1, 1, '')
FROM (
  /* get distinct list of LoadID */
  SELECT LoadID
  FROM loads_commissions
  GROUP BY LoadID
) id(LoadID)
CROSS APPLY (
  /* get list of CommissionTeam by LoadID into CSV using FOR XML */
  SELECT ','+CONVERT(VARCHAR(10), ct.CommissionTeam)
  FROM loads_commissions ct
  WHERE ct.LoadID = id.LoadID
  FOR XML PATH('')
) ct(CommissionTeamList)
;

Open in new window


SQL Fiddle: http://sqlfiddle.com/#!18/2a38c/1