0

Got this query:

SELECT TOP 100 
    i.item_id , 
    i.item_description, 
    part.part_number 
FROM 
     i 
LEFT JOIN 
     part ON part.item_id = i.item_id

An item usually have multiple part number. It is possible to modify my query to get a grouped result? without to add any subselects. Group by is not working. I tried to add FOR XML PATH('') but it transform all columns in xml, not only part number

ekad
  • 14,436
  • 26
  • 44
  • 46
acesta
  • 79
  • 11
  • You are looking for a `GROUP_CONCAT` or `LISTAGG` [workaround](http://stackoverflow.com/q/451415/314291). Sql Server doesn't have a simple "String.Join" function FWR. – StuartLC Jul 14 '15 at 06:21
  • First of all this looks like a desing issue. I think that there should be a 1:n relation between an item-table and a part-table. Anyway, I'll provide an exampel soon – Shnugo Jul 14 '15 at 06:23

1 Answers1

2

This is a possible way (still I think you should use a 1:n design)

DECLARE @items TABLE(item_id INT, item_description VARCHAR(100),part_number VARCHAR(100));
INSERT INTO @items VALUES(1,'HP Color','C4149A-REMAN')
                        ,(1,'HP Color','C4149REMAN')
                        ,(2,'Toner','480-0055')
                        ,(2,'Toner','4800055')
                        ,(2,'Toner','888037')
                        ,(3,'anotherone','1234');
WITH itemCTE AS
(
    SELECT DISTINCT item_id,item_description
    FROM @items
)                        
SELECT itemCTE.*,parts.list 
FROM itemCTE
CROSS APPLY
(
    SELECT STUFF((SELECT ', ' + part_number AS concated
                  FROM @items AS itm 
                  WHERE itm.item_id=itemCTE.item_id
                  FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'')
) AS parts(list)
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • I tried to run your code but I got this: Error Code: 1087, SQL State: S0002] Must declare the table variable "@items". – acesta Jul 14 '15 at 06:42
  • Did you copy the whole part? – Shnugo Jul 14 '15 at 06:46
  • Well, the DECLARE in the first line does the job. You can run this against your own table simply by changing @items to the real name of your table, Please tick as accepted, if this solves your problem, thx – Shnugo Jul 14 '15 at 07:10
  • I've maked some little canges and it worked, tnx – acesta Jul 14 '15 at 07:26