SELECT 'COM' AS LineDetailType,NULL AS WorkLocation, NULL AS CommentTickNumDate, NULL AS MiscCode UNION ALL SELECT 'INV' AS LineDetailType, hdr.WorkLocation, CONVERT(NVARCHAR(11),hdr.Date)+' '+ hdr.DailyWorkTicketNumber AS CommentTickNumDate, inv.MiscCode FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN dbo.MercerDailyWorkTicketInv AS inv ON hdr.DailyWorkTicketNumber = inv.DailyWorkTicketNumber WHERE (hdr.Misc_Text_Field4 = 'testinv1')
It gives me this output:
LineDetailType WorkLocation CommentTickNumDate MiscCode COM NULL NULL NULL INV MWSBOW Oct 24 2010 TESTINV1 10RIG INV MWSBOW Oct 24 2010 TESTINV2 10RIG PACK INV MWSBOW Oct 24 2010 TESTINV3 10BOP INV MWSBOW Oct 24 2010 TESTINV3 10RIG INV MWSBOW Oct 24 2010 TESTINV4 10CT INV MWSBOW Oct 24 2010 TESTINV4 10BOP INV MWSBOW Oct 24 2010 TESTINV4 10RIGXTO PACK INV MWSBOW Oct 24 2010 TESTINV5 10TRUCKING
The 'COM' on the 1st row is for Comment Line. The 'INV' is for invoice detail line. I am needing to combine all the CommentTickNumDate text data from the INV lines and place it in the CommentTickNumDate field of the COM row. Here is what the CommentTickNumDate field should look like for the output above for Row 1 or COM line:
Oct 24 2010 TESTINV1; Oct 24 2010 TESTINV2; Oct 24 2010 TESTINV3; Oct 24 2010 TESTINV4; Oct 25 2010 TESTINV5
I could do this if there were only so many rows or tickets going onto this Invoice; however, there can be 1 INV line or 40 INV lines or even 100 INV lines. Would anyone know of a way to accomplish this without creating 100 columns and then concantenating them all?
how to join text fields into one row column
Posted - 10/25/2010 : 19:20:57
