CODEWITHSUNDEEP
sqlsql-serversql-server-2005select
Dexter
Dexter

Reputation: 815

SQL: Nested SELECT with multiple values in a single field

In my SQL 2005 DB, I have a table with values stored as IDs with relationships to other tables. So in my MyDBO.warranty table, I'm storing product_id instead of product_name in order to save space. The product_name is stored in MyDBO.products.

When the marketing department pulls the demographic information, the query selects the corresponding name for each ID from related tables (trimmed down 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 on 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 "Accessories" that I did with "Product" and pull accessory_name from the MyDBO.accessories table using accessory_id. I'm not sure where to start, because first I'd need to extract the IDs and then somehow concatenate multiple values into a string. So each line would have "accessoryname1,accessoryname2,accessoryname3":

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 do I do this?

EDIT>> Posting my final 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

Upvotes: 4

Views: 32897

Answers (4)

pcofre
pcofre

Reputation: 4076

It seem to be a work for a concatenate aggregate function. In SQL it can be deployed using CLR

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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

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

Upvotes: 3

Sean
Sean

Reputation: 7670

You could write a table valued function that simply splits comma separated string into XML and turns XML nodes to rows.

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

Join to accessories through the result of function call, and stuff the result back to comma separated list of names.

Untested 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

Upvotes: 5

Paul Sasik
Paul Sasik

Reputation: 81557

You just need to use the FOR XML feature of SQL Server to easily cat strings:

Example from the linked 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 stored as comma delimited you will have to write a UDF that parses the field and returns a table which can then be used with an IN predicate in your WHERE clause. Look here for starters, and here.

Upvotes: 1

Related Questions