SytelineMaster

The Select … for XML can be useful.  Let say that you want to list out Job Order information, one job per line, and one of the field will be WC, and you want to show all WCs for the job, in the format like WC1, WC2, WC3, …  Here is the select statement will work the trick.

Select j.job, j.suffix, j.order_date, j.item, j.qty_released,

(Select jr.wc + ‘,  ‘  as ‘text()’ from jobroute jr where jr.job = j.job and jr.suffix = j.suffix

  for XML path(”)) as ‘WC’

from job j where j.status = ‘R’