combining duplicate results per itemid, 6 -7 images per itemid

Question

Referring to How to make a query with group_concat in sql server i referenced the above link to resolve the duplicates and combine multiple images in the same field (much like the one referenced in that link) but instead of getting 6-7 images per item, i'm getting all images for all items in every item. also with joined tables, group by isn't working.

select ItemId, Pictures = STUFF((
      SELECT '|' + Title
      FROM dbo.Pictures
      WHERE ItemID = ItemID 
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 

from dbo.Pictures
group by ItemID

group by works with this piece of code but with i get images for all items in each item.

the code that i need this to work with is,

USE SixBit;
GO
SELECT dbo.Active.[Item ID], CONCAT (SixBit.dbo.Active.[Stock Number], '  ',  Active.Stock) AS StockNumber,      
CONCAT(SixBit.dbo.Active.Year, '  ', SixBit.dbo.Active.Model, ' / Stock Number - ',  Active.[Stock Number], '  ', Active.Stock, ' / ', Active.Title, ' Interchange Part Number  / ', Active.[Interchange Part Number], '/ ITEM CONDITION ', Active.[eBay Condition  Description], Active.[Conditions and Options]
) AS Descrition, CONCAT(Active.Year, '  ', Active.Model) AS Category, Active.Title AS  Name, CONCAT(SixBit.dbo.Active.Year, '  ', SixBit.dbo.Active.Model, ' / Stock Number - ',  Active.[Stock Number], '  ', Active.Stock, ' / ', Active.Title,' Interchange Part Number / ', Active.[Interchange Part Number], '/ ITEM CONDITION ', Active.[eBay Condition  Description], Active.[Conditions and Options]
) AS DescritionLong, Active.[Weight Major], Active.[Dimension Length], Active.[Dimension Width], Active.[Dimension Depth], Active.[Qty On Hand], Active.[Fixed Price], Active.SKU, Active.Model AS Manufacture, dbo.Listings.StartDate, 
Pictures = STUFF((
      SELECT '|' + CONCAT ('"', dbo.Pictures.Title, '"')
      FROM dbo.Pictures
      WHERE ItemID = ItemID
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 




FROM            Listings INNER JOIN
                     Active ON Listings.ListingID = Active.[Item ID] INNER JOIN
                     Pictures ON Active.[Item ID] = Pictures.ItemID


where dbo.Active.[Item ID] <= 10

group by doesn't work with this code and like above, all images are in every item, so all items have all images. p.s, the concat i'm using in STUFF is because the results contain "$" and i need to wrap them in " " for importing purposes.

thanks in advance for any help i can get.

I am guessing from the code that this is SQL Server. Please tag questions with the appropriate database. — Gordon Linoff, Feb 09 '14 at 23:43
No need to apologize. You are new to asking questions here and proper tagging helps you get the best answer quickly. — Gordon Linoff, Feb 10 '14 at 01:20

Gordon Linoff · Answer 1 · 2014-02-10T01:17:04.923

You need to properly correlated your subquery. The expression:

  WHERE ItemID = ItemID

Always evaluates to true in your inner query (unless ItemId could be NULL). You need table aliases to distinguish between the inner and outer table. This is the resulting query:

select p.ItemId, Pictures = STUFF((
      SELECT '|' + p2.Title
      FROM dbo.Pictures p2
      WHERE p.ItemID = p2.ItemID 
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
from dbo.Pictures p
group by p.ItemID;

Also, I think the 2 should be a 1. The third argument to stuff() is the length, so this is removing the first two characters of the result. However, you are only pre-pending one character, so this seems to be eating the first character of the first title.

EDIT:

The query for the "longer" version should look something like the following. Table aliases help make the query more readable:

SELECT a.[Item ID], CONCAT(a.[Stock Number], '  ',  a.Stock) AS StockNumber,      
       CONCAT(a.Year, '  ', a.Model, ' / Stock Number - ',  a.[Stock Number], '  ',
              a.Stock, ' / ', a.Title, ' Interchange Part Number  / ',
              a.[Interchange Part Number], '/ ITEM CONDITION ',
              a.[eBay Condition  Description], a.[Conditions and Options]
             ) AS Description,
       CONCAT(a.Year, '  ', a.Model) AS Category, a.Title AS Name,
       CONCAT(a.Year, '  ', a.Model, ' / Stock Number - ',  a.[Stock Number], '  ',
              a.Stock, ' / ', a.Title,' Interchange Part Number / ',
              a.[Interchange Part Number], '/ ITEM CONDITION ',
              a.[eBay Condition  Description], a.[Conditions and Options]
             ) AS DescriptionLong,
       a.[Weight Major], a.[Dimension Length], a.[Dimension Width], a.[Dimension Depth], 
       a.[Qty On Hand], a.[Fixed Price], a.SKU, a.Model AS Manufacture, 
       l.StartDate, 
       Pictures = STUFF((SELECT '|' + CONCAT ('"', p2.Title, '"')
                         FROM dbo.Pictures p2
                         WHERE p2.ItemID = a.[Item ID]
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
                        ), 1, 1, '') 
FROM Listings l INNER JOIN
     Active a
     ON l.ListingID = a.[Item ID]
where a.[Item ID] <= 10;

Wow, thanks that works great. going to try it with the big code. — user3290887, Feb 09 '14 at 23:55
that long versions works good, i am getting duplicates tho... yes it is sgl server... i'll remember about tagging correctly — user3290887, Feb 10 '14 at 00:56
also the results look good but when i try to export to excel, the 1st " is missing — user3290887, Feb 10 '14 at 01:06
You are getting duplicates because (at least) you are joining to the `pictures` table in the outer query. I left that in, even though it did not really make sense, because it was in the original query. — Gordon Linoff, Feb 10 '14 at 01:17
ok, thanks Gordon, should i use a different join? i need that table to get the pics related to the active items. — user3290887, Feb 10 '14 at 01:25

combining duplicate results per itemid, 6 -7 images per itemid

1 Answers1