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!

SQL

Last Comment

Kevin Cross

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Pavel Celba

1/26/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Kevin Cross

1/26/2020

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

Certified Expert Program

Credly Partnership

Udemy Partnership