how to join text fields into one row column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to join text fields into one row column

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-10-25 : 19:20:57

I have the following query:

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?

Annn123
Starting Member

1 Post

Posted - 2010-10-26 : 00:00:09

unspammed