SQL: nested SELECT with multiple values ​​in one field

Question

In my SQL 2005 database, I have a table with values ​​stored as identifiers with relationships to other tables. Therefore, in my MyDBO.warranty table , I save product_id instead of product_name to save space. ProductName is stored in MyDBO.products .

When the marketing department retrieves demographic information, the query selects the appropriate name for each identifier from the related tables (short for brevity):

SELECT w1.warranty_id AS "No.", w1.created AS "Register Date" w1.full_name AS "Name", w1.purchase_date AS "Purchased", ( SELECT p1.product_name FROM WarrDBO.products p1 WITH(NOLOCK) WHERE p1.product_id = i1.product_id ) AS "Product Purchased", i1.accessories FROM WarrDBO.warranty w1 LEFT OUTER JOIN WarrDBO.warranty_info i1 ON i1.warranty_id = w1.warranty_id ORDER BY w1.warranty_id ASC 

Now my problem is that the “accessories” column in the warranty_info table stores several values:

 No. Register Date Name Purchased Accessories --------------------------------------------------------------------- 1500 1/1/2008 Smith, John Some Product 5,7,9 1501 1/1/2008 Hancock, John Another 2,3 1502 1/1/2008 Brown, James And Another 2,9 

I need to do something similar with the “Accessories” that I did with the “Product” and pull the address_name from the MyDBO.accessories table using the accessory_name strong>. I'm not sure where to start, because first I will need to extract the identifiers, and then somehow combine several values ​​into a string. Thus, each line will have "application_name1, application_name2, computer_name3":

 No. Register Date Name Purchased Accessories --------------------------------------------------------------------- 1500 1/1/2008 Smith, John Some Product Case,Bag,Padding 1501 1/1/2008 Hancock, John Another Wrap,Label 1502 1/1/2008 Brown, James And Another Wrap,Padding 

How to do it?

EDIT → Posting my last code:

I created this function:

 CREATE FUNCTION SQL_GTOInc.Split ( @delimited varchar(50), @delimiter varchar(1) ) RETURNS @t TABLE ( -- Id column can be commented out, not required for sql splitting string id INT identity(1,1), -- I use this column for numbering splitted parts val INT ) AS BEGIN declare @xml xml set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>' insert into @t(val) select r.value('.','varchar(5)') as item from @xml.nodes('//root/r') as records(r) RETURN END 

And updated my code accordingly:

 SELECT w1.warranty_id, i1.accessories, ( CASE WHEN i1.accessories <> '' AND i1.accessories <> 'NULL' AND LEN(i1.accessories) > 0 THEN STUFF( ( SELECT ', ' + a1.accessory FROM MyDBO.accessories a1 INNER JOIN MyDBO.Split(i1.accessories, ',') a2 ON a1.accessory_id = a2.val FOR XML PATH('') ), 1, 1, '' ) ELSE '' END ) AS "Accessories" FROM MyDBO.warranty w1 LEFT OUTER JOIN MyDBO.warranty_info i1 ON i1.warranty_id = w1.warranty_id 

Answer 1

You can write a table-dependent function that simply splits a comma-separated string into XML and turns the XML nodes into strings.

See: http://www.kodyaz.com/articles//t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Join the accessories through the result of calling the function and enter the result back into the list of names, separated by commas.

Unverified code:

 SELECT w1.warranty_id AS "No.", w1.created AS "Register Date" w1.full_name AS "Name", w1.purchase_date AS "Purchased", ( SELECT p1.product_name FROM WarrDBO.products p1 WITH(NOLOCK) WHERE p1.product_id = i1.product_id ) AS "Product Purchased", STUFF( ( SELECT ', ' + a.name FROM [table-valued-function](i1.accessories) acc_list INNER JOIN accessories a ON acc_list.id = a.id FOR XML PATH('') ), 1, 1, '' ) AS [accessories] FROM WarrDBO.warranty w1 LEFT OUTER JOIN WarrDBO.warranty_info i1 ON i1.warranty_id = w1.warranty_id ORDER BY w1.warranty_id ASC 

Answer 2

Nothing to do with your question. Just note that your original query can also be written by moving the subqery to join, like:

 SELECT w1.warranty_id AS "No.", w1.created AS "Register Date" w1.full_name AS "Name", w1.purchase_date AS "Purchased", p1.product_name AS "Product Purchased", i1.accessories FROM WarrDBO.warranty w1 INNER JOIN WarrDBO.products p1 ON p1.product_id = i1.product_id LEFT OUTER JOIN WarrDBO.warranty_info i1 ON i1.warranty_id = w1.warranty_id ORDER BY w1.warranty_id ASC 

Answer 3

You just need to use FOR XML for SQL Server to easily write lines:

Example from a related blog post:

 SELECT STUFF( ( SELECT ' ' + Description FROM dbo.Brands FOR XML PATH('') ), 1, 1, '' ) As concatenated_string 

To parse a field that has already been saved as a comma separator, you will need to write a UDF that parses the field and returns a table that can then be used with the IN predicate in your WHERE clause. Look here for starters and here .

Answer 4

This seems to work for the aggregate concatenation function. In SQL, it can be deployed using the CLR

http://www.mssqltips.com/tip.asp?tip=2022