Using For XML Path to Concatenate in a Sub-Query
-
Hello,
I'm hoping I can get some guidance on how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.
But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.
My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):
SelectDM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
DSD.ShiftID [ShiftID],
Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic],
Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
-- Mobile Vehicles
(Select isnull(MSM.ShortDesc,'') From rpt_MobileSetupMaster MSM Where MobileType=1 and MobileID In
(Select MobileID From rpt_DriveShiftMobileDetail DSMD Where DSMD.ShiftID In
(Select ShiftID From rpt_DriveShiftDetail DSD Where DSD.DriveID = DM.DriveID))) [Mobile_Short_Desc]
From rpt_DriveMaster DM
Left Outer Join rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join rpt_CenterDetail CD on DM.CenterID=CD.CenterID
I've read articles such as http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/[/url] but I'm not sure how or if i can incorporate it into my sub-query to for vehicles.
Any suggestions on the best way to handle this?
And not sure if this should be in the SQL Server Newbie or here either. I'm a newbie as well.
-
Hi,
Try:
STUFF( (Select ', ' + isnull(MSM.ShortDesc,'')From From rpt_DriveShiftDetail DSD
Inner Join rpt_DriveShiftMobileDetail DSMD
On DSMD.ShiftID = DSD.ShiftID
Inner Join rpt_MobileSetupMaster MSM
On MSM.MobileType = 1 and MSM.MobileID = DSMD.MobileID
Where DSD.DriveID = DM.DriveID
For XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as [Mobile_Short_Desc]
Hope this helps.
-
That actually works great, thank you very much.
One last thing, is there a way to select distinct records in this command?
For example, while a drive may have multiple shifts, a vehicle could then be assigned to multiple while I would like it to be displayed only once?
-
Check out this article on the topic. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You can add Distinct:
STUFF( (Select Distinct ', ' + isnull(MSM.ShortDesc,'')Hope this helps.
-
Thanks again imex,
Last question I promise. Adding Distinct does work, but with or without it, I am noticing it is trimming the the first letter word/number off the Vehicle Description.
For example, all of the vehicles should begin with "M" followed by a 3 digit number. But when I run the report in SSRS or SSMS the "M" is trimmed off the first record, but if multiple vehicles are displayed, all records after the first are fine:
Is there a way around this?
-
treyagrimes (2/18/2015)
Thanks again imex,Last question I promise. Adding Distinct does work, but with or without it, I am noticing it is trimming the the first letter word/number off the Vehicle Description.
For example, all of the vehicles should begin with "M" followed by a 3 digit number. But when I run the report in SSRS or SSMS the "M" is trimmed off the first record, but if multiple vehicles are displayed, all records after the first are fine:
Is there a way around this?
Of course there is a way to fix this. You need to look at the documentation and understand what your code is doing.
https://msdn.microsoft.com/en-us/library/ms188043.aspx
Note the third parameter to STUFF is length. In the code posted above it is 2. You need to change that to a 1.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I believe you removed the blank space after the comma, then you must change the third parameter of the function Stuff to 1:
,1, 1, '') as [Mobile_Short_Desc]Hope this helps.
-
Thanks, I'll read that article on STUFF now to try and understand.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply