CODEWITHSUNDEEP
sqlsql-server
Monika
Monika

Reputation: 61

Combine multiple rows in a single row by descending order SQL

I have this table below:

Style   | Color   | Qty
------------------------
Style1  | Red     | 10
Style1  | Black   | 15
Style1  | White   | 7
Style2  | Yellow  | 10
Style2  | Green   | 8
Style3  | White   | 15
Style3  | Black   | 20

and I want to get a table, which will have Style and then all its colors separated with comma, but in descending order of their available QUANTITIES (Qty); and SUM of Qty in another column. I have the code which gives me almost what I want, but I don't know how to modify it so as I have colors in correct order. 

SELECT DISTINCT A.Style, sum(Qty) as  SumQty,  
  STUFF((SELECT distinct ',' + p.Color  
         FROM inv  as P  
         WHERE A.Style = p.Style  
            FOR XML PATH(''), TYPE  
            ).value('.', 'NVARCHAR(MAX)')  
        ,1,1,'') AS Color  
from inv As A  
group by A.style

Result table should look:

Style   |     Color          | SumQty
-------------------------------------
Style1  | Black, Red, White  |   32
Style2  | Yellow, Green      |   18
Style3  | Black, White       |   35

I would greatly appreciate any help.

Upvotes: 1

Views: 184

Answers (2)

Xedni
Xedni

Reputation: 4695

;with src (Style, Color, Qty) as
(
    select 'Style1', 'Red', 10 union all
    select 'Style1', 'Black', 15 union all
    select 'Style1', 'White', 7 union all
    select 'Style2', 'Yellow', 10 union all
    select 'Style2', 'Green', 8 union all
    select 'Style3', 'White', 15 union all
    select 'Style3', 'Black', 20
)
select 
    Style,
    SumQty = sum(Qty),
    Color = stuff((select ',' + i.Color
                   from src i
                   where o.Style = i.Style
                   order by i.Qty desc
                   for xml path('')), 1, 1, '')
from src o
group by Style

You don't need the type and value() parts of your FOR XML

I felt the need to correct this. 9/10 times, you don't need the value and type in there. However if the text you're serializing contains special characters like >, < or several other characters, they'll get HTML-encoded. So if you have such characters, doing it your way is actually the only way to keep them as such. I didn't know this as of yesterday but started messing around with it today after seeing your post, so thanks!

Example:

declare @gt xml = '>'

select 
    RawXML = @gt, -- > XML, encoded as &gt;
    ImplicitCast = cast(@gt as varchar(10)), -- varchar, encoded as &gt;
    ValuesMethod = @gt.value('.', 'varchar(10)') -- varchar, encoded as >

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just need an ORDER BY in the subquery:

select i.Style, sum(i.Qty) as SumQty,
       STUFF( (SELECT ',' + i2.Color
               FROM inv i2
               WHERE i2.Style = i.Style
               ORDER BY i2.Qty DESC
               FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'
                     ), 1, 1,'') AS Color
from inv i
group by A.style;

Upvotes: 1

Related Questions