CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 1938

for xml path not concatenating rows in a SQL query in SQL Server

I am having trouble with some code that was given to me. It isn't concatenating the rows as it should be. I am pulling from 3 tables to select which rows to concatenate. I have a Comment, WorkOrderT and WorkMaterial_nonFiltered table.

The Comment table, obviously, has comments and is linked to the WorkMaterial_nonFiltered table. The WorkMaterial_nonFiltered table is connected to the WorkOrderT table which is where the Work Order ID's are.

I am trying to use the Work Order ID to get all the comments for that work order and have them all concatenated into one row per work order id. Here is the code as I currently have it:

select 
    wo.WOId as ID,
    STUFF(cast((select distinct '; ' + c.comments
                from working.Comment c
                where c.Instance_InstanceId = wm.id
                for xml path(''), type) as varchar(max)), 1, 1, ' ') as MaterialComments
from 
    working.WorkOrderT wo
left join 
    working.WorkMaterial_nonFiltered wm on wm.WorkOrder = wo.WOId
where 
    wo.WOId = '00559FB6-4DD2-4762-8DE1-8D1B13962AED' 
order by     
    [MaterialComments]

When I run this I get 11 rows as a result. The first has the MaterialComments as NULL then the others have data in them. I don't care about the NULL row, unless it's what's causing the problem, it's the others that I really need to have concatenated, separated by the ";". I've tried building this out one step at a time, but I've not been able to figure out why I always get 11 rows instead of just the 1. I've looked into the following other questions to try and find a solution:

Concatenate many rows into a single text string?

Concatenate Rows using FOR XML PATH() from multiple tables

How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

How to make a query with group_concat in sql server

String Aggregation in the World of SQL Server

So far I've not been able to get any suggestions from these links to work for me I still get 11 rows instead of 1 concatenated row.

EDIT

Here is some sample data from what I have in my tables: (Note sure how to make this an actual table sorry for the sloppy formatting)

WorkOrderID                             Comments  
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/17 caj in transit; expected delivery on 5/18 per ups 1Z25AR580319345668
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/18 caj updated esd on 6/17 per vendor site  
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/17 caj allocated to ship  
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/17 caj updated esd on 5/27 per vendor site  
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/18 caj processed; no udpated delivery date per estes Tracking #/BOL #:    3SNS31780960  
00559FB6-4DD2-4762-8DE1-8D1B13962AED    5/18 caj processed; no updated delivery date per ups 1Z39ER600354622348  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A    Jun 25 2015 10:22AM;dlb223;6/25 dlb 1Z4370490304215160 to be dlv'd today  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A    Jun  8 2015  1:11PM;klh323;6/8 klh Reserved to meet 06/30 requested delivery date  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A    Jun 25 2015 10:23AM;dlb223;6/23/2015  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A    Jun 25 2015 10:23AM;dlb223;6/5 dlb 1Z4370490304215937 to be dlv'd today  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A    Jun 25 2015 10:24AM;dlb223;6/25 dlb 1Z4370490304216445 to be dlv'd today  
00910B84-486C-4AD4-9B1E-5F8D8B42C841    5/12 jad IN TRANSIT; EXPECTED DELIVERY 5/12 PER UPS 1Z750WA20313280446  
00910B84-486C-4AD4-9B1E-5F8D8B42C841    4/29 jad IN TRANSIT; EXPECTED DELIVERY 4/29 PER UPS 1Z39ER600354244542

The results I want to look like this: (again I don't know how to make this a table)

WorkOrderID                                     Comments
00559FB6-4DD2-4762-8DE1-8D1B13962AED            5/17 caj in transit; expected delivery on 5/18 per ups 1Z25AR580319345668; 5/18 caj updated esd on 6/17 per vendor site; 5/17 caj allocated to ship; 5/17 caj updated esd on 5/27 per vendor site; 5/18 caj processed; no udpated delivery date per estes Tracking #/BOL #:    3SNS31780960; 5/18 caj processed; no updated delivery date per ups 1Z39ER600354622348  
008FC1D1-D6A6-4E48-A69F-168DBF8D215A            Jun 25 2015 10:22AM;dlb223;6/25 dlb 1Z4370490304215160 to be dlv'd today; Jun  8 2015  1:11PM;klh323;6/8 klh Reserved to meet 06/30 requested delivery date; Jun 25 2015 10:23AM;dlb223;6/23/2015; Jun 25 2015 10:23AM;dlb223;6/5 dlb 1Z4370490304215937 to be dlv'd today; Jun 25 2015 10:24AM;dlb223;6/25 dlb 1Z4370490304216445 to be dlv'd today  
00910B84-486C-4AD4-9B1E-5F8D8B42C841            5/12 jad IN TRANSIT; EXPECTED DELIVERY 5/12 PER UPS 1Z750WA20313280446; 4/29 jad IN TRANSIT; EXPECTED DELIVERY 4/29 PER UPS 1Z39ER600354244542

I hope that this makes it more clear what I'm trying to accomplish.

Upvotes: 1

Views: 417

Answers (2)

Ben
Ben

Reputation: 35663

Explicitly ask for a text node within an element, rather than an element.

select 
    wo.WOId as ID,
    (select distinct '; ' + c.comments as "text()"
                from working.Comment c
                where c.Instance_InstanceId = wm.id
                for xml path(''), type) as "MaterialComments"
from 
    working.WorkOrderT wo
left join 
    working.WorkMaterial_nonFiltered wm on wm.WorkOrder = wo.WOId
where 
    wo.WOId = '00559FB6-4DD2-4762-8DE1-8D1B13962AED' 
order by     
    [MaterialComments]

Upvotes: 0

Mike
Mike

Reputation: 1938

I have ended up using pass through queries to a linked server. That is where the problem actually was. With the data coming back from the linked server. Once I figured that out I was able to get the results that I needed. Now I have a really long pass through query that uses the for xml and gives me the comments for the WOID's the way that I need them.

Upvotes: 1

Related Questions