Thread: Need help w/ an SQL statement

Ok I've used the following SQL statement to determine which xmlpackages are being used and by how many products.

Code:

SELECT XMLPACKAGE,
COUNT(ProductID) AS NumOccurrences
FROM Product
GROUP BY XMLPACKAGE
HAVING ( COUNT(ProductID) > 0 )

Now i want to list out the products ids using each of the xmlpackages. If I need to run a query for each xmlpackage that would be fine and that is what i've tried but to no sucess.

Thanks in advance for your help!
David

Something like this may help (provided you don't have to recurse more than 32767 times...that's all the more recursion sql can handle)

Code:

WITH Ranked ( XmlPackage, rnk, ProductID ) 
             AS ( SELECT XmlPackage,
                         ROW_NUMBER() OVER( PARTITION BY XmlPackage ORDER BY XmlPackage ),
                         CAST( ProductID AS VARCHAR(8000) )
                    FROM Product),
   AnchorRanked ( XmlPackage, rnk, ProductID ) 
             AS ( SELECT XmlPackage, rnk, ProductID
                    FROM Ranked
                   WHERE rnk = 1 ),
RecurRanked ( XmlPackage, rnk, ProductID )
             AS ( SELECT Xmlpackage, rnk, ProductID
                    FROM AnchorRanked
                   UNION ALL
                  SELECT Ranked.XmlPackage, Ranked.rnk,
                         RecurRanked.ProductID + ', ' + Ranked.ProductID
                    FROM Ranked
                   INNER JOIN RecurRanked
                      ON Ranked.XmlPackage = RecurRanked.XmlPackage
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT XmlPackage, MAX( ProductID ) as ProductIDs
      FROM RecurRanked
  GROUP BY XmlPackage
  OPTION (maxrecursion 32767)

If you do need more recursion than that, you'll get an error message like The statement terminated. The maximum recursion 3 has been exhausted before statement completion. If this is the case, you'll be better off writing a custom page that you can access from the admin site that completes this logic for you using the data returned from your query/queries (i.e. query for distinct xmlpackages from the product table, then query for all productids where the xmlpackage matches each of the xmlpackages retrieved)...it's much easier to do it that way than in sql.

Something like this may work for you as well

Code:

SELECT p1.XmlPackage,
       ( SELECT CAST(ProductID as varchar) + ','
           FROM Product p2
          WHERE p2.XmlPackage = p1.XmlPackage
          ORDER BY ProductID
            FOR XML PATH('') ) AS ProductIDs
      FROM Product p1
      WHERE p1.XmlPackage IS NOT NULL
      GROUP BY XmlPackage

Thanks so much for your quick reply! I'll give this a try.
-David