SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to join text fields into one row column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

osupratt
Posting Yak Master

233 Posts

Posted - 10/25/2010 :  19:20:57  Show Profile  Reply with Quote
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 Posts

Posted - 10/26/2010 :  00:00:09  Show Profile  Reply with Quote
unspammed
Go to Top of Page

osupratt
Posting Yak Master

233 Posts

Posted - 10/27/2010 :  10:39:16  Show Profile  Reply with Quote
Thanks for trying to sell me boots???

Anyhow, have figured this post out.
Go to Top of Page

lili123
Starting Member

China
2 Posts

Posted - 12/06/2010 :  02:16:04  Show Profile  Reply with Quote
unspammed
Go to Top of Page

lili123
Starting Member

China
2 Posts

Posted - 12/06/2010 :  02:18:58  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000