We help IT Professionals succeed at work.

SQL to transform records into a string value

newknew
newknew asked
on
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!
Comment
Watch Question

You may use this simple query in SQL 2017 and higher:
SELECT LoadID, STRING_AGG(CommissionTeam, ',') AS CommissionString 
  FROM loads_commissions 
 GROUP BY LoadID 

Open in new window

MySQL offers GROUP_CONCAT() function for this purpose.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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