Apr 19th 2008 06:05 pm
Concatenating values in t-sql select statements
In a previous life, I worked on purchasing software. One of the performance problems that continually plagued us stemmed from a fundamental decision we made about how we tracked the line items of an order:
|
orderLineId |
orderId |
qty |
product |
|
1 |
123 |
1 |
Product A |
|
2 |
123 |
1 |
Product A |
|
3 |
123 |
1 |
Product B |
|
4 |
123 |
1 |
Product C |
Each line has quantity one because throughout the application users would changed where a portion of an order was billed, shipped or assembled. It seemed easier to simply store each as an individual line of quantity one than worry about how splitting lines would percolate data changes in the application.
What happens when you have quantity 100? How do you present that to the user? How do you persist changes back to the database? We realized the need to group like items for the user to avoid making him select 100 rows manually. This led to, “How do we associate the one row in the UI with the corresponding 100 rows in the database?”.
The answer was relatively simple. Instead of storing a single id for the row, we stored all 100 ids comma delimited.
In grouping like items, we had to add a sub-query to select all the ids of the line item records aggregated for the result record.
In short:
select STUFF(
(
SELECT ',' + convert(varchar(12), object_id)
FROM sys.objects
FOR xml PATH('')
), 1, 1, '')
giving a result set of:
| 4,5,7,8,13,15,… |
We’re selecting the id and converting it to a string.
the For xml PATH(”) indicates that our output should be xml, but that the “path” for each record is an empty string.
Given a result set of:
| 4 |
| 5 |
| 7 |
| 8 |
| 13 |
| 15 |
we “convert” it to an xml path of “” and get
| ,4,5,7,8,13,15 |
the STUFF function simply removes the first prepended “,” from our result.
Using this we were able to take user changes from the UI and apply them to the records in the database through a single UPDATE statement using the clause “where orderLineId in (@lineIds)”, @lineIds = our delimited listing.
Happy coding.
2 Comments »
chi on 30 Oct 2008 at 1:59 pm #
I really like this solution because I don’t have to add more objects to the DB just to generate some ad-hoc report request. The only major issue that I have so fwar is that the result is limited to 256 characters. It’s more apparant if you select the object name:
select STUFF(
(
SELECT ‘,’ + convert(varchar(12), object_id)
FROM sys.objects
FOR xml PATH(”)
), 1, 1, ”)
Any idea how to resolve this issue? Highly appreciated!
phil on 30 Oct 2008 at 7:14 pm #
I don’t understand your issue. The folowing query in SQL 2005:
select STUFF(
(
SELECT ‘,’ + object_name(object_id)
FROM sys.objects
FOR xml PATH(”)
), 1, 1, ”)
returns a result well over 256 characters:
sysrowsetcolumns,sysrowsets,sysallocunits,sysfiles1,syshobtcolumns,syshobts,sysftinds,sysserefs,sysowners,sysdbreg,sysprivs,sysschobjs,syslogshippers,syscolpars,sysxlgns,sysxsrvs,sysnsobjs,sysusermsgs,syscerts,sysrmtlgns,syslnklgns,sysxprops,sysscalartypes,systypedsubobjs,sysidxstats,sysiscols,sysendpts,syswebmethods,sysbinobjs,sysobjvalues,sysclsobjs,sysrowsetrefs,sysremsvcbinds,sysxmitqueue,sysrts,sysconvgroup,sysdesend,sysdercv,syssingleobjrefs,sysmultiobjrefs,sysdbfiles,sysguidrefs,syschildinsts,sysqnames,sysxmlcomponent,sysxmlfacet,sysxmlplacement,sysobjkeycrypts,sysasymkeys,syssqlguides,sysbinsubobjs,spt_fallback_db,spt_fallback_dev,spt_fallback_usg,QueryNotificationErrorsQueue,queue_messages_1003150619,EventNotificationErrorsQueue,queue_messages_1035150733,ServiceBrokerQueue,queue_messages_1067150847,spt_monitor,spt_values,DF__spt_value__statu__436BFEE3,MSreplication_options,sp_MSrepl_startup,sp_MScleanupmergepublisher
Is it possible your query tools is truncating the result?