Need a query how to get this scenario

Question

I need this scenario based query.

SalesID Line    Noofitems   Category    Desc
SID12345    1   1           Metal-SI    Strong iron
SID12345    2   1           Plastic     Disposal
SID12345    3   1           Plastic     Disposal

Expected Output

SalesID     Totitems    Category            Desc
SID12345    3           Metal-SI,Plastic    Strong iron,Disposal

You’re not really providing enough information in your quesiton. If you’re asking what SQL statement would produce that output, then I’d suggest you explicitly say that in your question. — sideshowbarker, Aug 30 '15 at 03:13

score 0 · Answer 1 · answered Aug 30 '15 at 03:14

You can use for xml path approach here, with a distinct clause to remove duplicates while concatenating column values, followed by a group by on SalesID

Query will be like:

select 
    SalesID, 
    sum(Noofitems) as Totitems,
    STUFF(
            (
                Select 
                    DISTINCT ','+t1.Category 
                from tbl t1 
                    where t1.SalesID=t2.SalesID 
                for xml path('')
            ),1,1,'') 
            as Category,
    STUFF(
            (
                select 
                    DISTINCT ','+t1.[Desc] 
                from tbl t1 
                    where t1.SalesID=t2.SalesID 
                for xml path('')
            ),1,1,'') 
        as [Desc]
from tbl t2 
    group by SalesID

Link for demo sql fiddle: http://sqlfiddle.com/#!6/d6bf5/8

Use order by in the inner query to change the order of concatenated strings.

Need a query how to get this scenario

1 Answers1