SQL: SELECT Imbriquée avec plusieurs valeurs dans un champ unique

Dans mon SQL 2005 DB, j'ai un tableau avec des valeurs stockées en tant que Id avec des relations avec d'autres tables. Donc, à mon MyDBO.garantie table, je suis le stockage product_id au lieu de produit_nom, afin d'économiser de l'espace. Le produit_nom est stocké dans MyDBO.produits.

Lorsque le service marketing tire les informations démographiques, la requête sélectionne le nom correspondant pour chaque ID de tables liées (réduite pour des raisons de concision):

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

Maintenant, mon problème, c'est que les "accessoires" de la colonne sur la warranty_info table stocke plusieurs valeurs:

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

J'ai besoin de faire quelque chose de similaire avec les "Accessoires" que j'ai fait avec "Produit" et de tirer accessory_name de la MyDBO.accessoires table à l'aide de accessory_id. Je ne suis pas sûr où commencer, parce que d'abord j'avais besoin d'extraire les Id et puis en quelque sorte concaténer plusieurs valeurs dans une chaîne de caractères. De sorte que chaque ligne aurait "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

Comment puis-je faire cela?

EDIT>> Poster mon code final:

J'ai créé cette fonction:

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

Et mis à jour mon code en conséquence:

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

OriginalL'auteur Dexter | 2011-04-01