Help Using A Tally Table To Build A Comma-Separated String

• SSSolice

  SSCommitted

  Points: 1907

  More actions

  August 12, 2009 at 1:32 pm

  #140809

  Hi Everyone,

  I need some help with the logic to use a tally table to accomplish the following. I think it can be done, but not sure how to go about it.

  I have a receipt number (tblTransControl) that can have of one or many vendors (tblVendors) associated with it. Also in the tblTransControl with the receipt number are dollar amounts for charges and payroll. I want to show the receipt number, money amounts, and vendor list on a single row.

  Select receipt, vendor, charge, pay

  From tblTransControl as c

  Join tblVendor as v

  On c.guidControl = v.guidControl

  Returns:

  Receipt Vendor Charge Pay

  123 abc 10 3

  123 xyz 10 3

  123 qrs 10 3

  What I wan to return is:

  Receipt Vendors Charge Pay

  123 abc, xyz, qrs 10 3

  Thanks very much for the help.

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  August 12, 2009 at 1:43 pm

  #1038259

  I quit using tally tables for that because the XML Path method ran faster in my tests. Barry gave a very good explanation of why in a post here.

  Here are a couple of samples from my toolbox.

  ;with someTable (foo) as-- just using this cte as an example table

  (select 'A' union all

  select 'B' union all

  select 'C' union all

  select 'D'

  )

  -- the STUFF is used to get rid of the first comma, which will be at position 1 within the string

  select stuff((

  SELECT ',' + foo

  FROM someTable

  ORDER BY foo

  FOR XML PATH('')

  ),1,1,'') as [Concatenated Foo]

  I think this second example will be the most useful to you. You appear to be grouping on the receipt, charge, and pay columns. Let me know if you have any questions.

  ---------------------------------------------------------------------------------------------

  -- more sophisticated example, using a WHERE clause in the subquery and GROUP BY in the main

  -- query to do concatenation for all combinations in the input table

  ---------------------------------------------------------------------------------------------

  declare @data table (empname varchar(30), svcdate varchar(10), wrkGroup int)

  insert into @data

  Select 'Henry' as empname, '10/20/1960' as svcDate,2 as wrkGroup union all

  Select 'Henry', '10/20/1960',3 union all

  Select 'Henry', '10/20/1960',5 union all

  Select 'Steve','08/17/1965',8 union all

  Select 'Steve','08/17/1965',10 union all

  Select 'Steve','08/17/1965',9 union all

  Select 'Steve','08/17/1965',4 union all

  Select 'Steve','08/17/1965',2 union all

  Select 'Laura','09/12/1967',3

  select * from @data

  select empname,svcDate,stuff((SELECT ',' + cast(wrkGroup as varchar(5))

  FROM @data d2

  WHERE d2.empName = d1.empname and d2.svcDate = d1.svcDate -- must match GROUP BY below

  ORDER BY wrkGroup

  FOR XML PATH('')

  ),1,1,'') as [Concatenated]

  from @data d1

  GROUP BY empName,svcDate -- without GROUP BY multiple rows are returned

  order by empName,svcDate

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills