I have a table like this in SQL Server:

• "ID" is an identity field.
• Rank is an always increasing integer that does the same job as "Tag"
• "Tag" groups rows together into Single or Multiple type rows: if "Tag" is blank or null the row is selected by itself (single). If 'A', 'B' 'C' or 'D' they must all be rolled up to one 'A' (Multiple) row by concatenating the "Name" fields separated by commas.
• The "Size" value for the rolled-up rows is the value for the 'A' row. For all rows with 'B','C' or 'D' Size is zero and on rollup will take on the previous 'A' value.
• Finally, the Seq field is a user-specified sort order value the final ORDER BY Field.

So, I need the following output:

I know I need sub queries and some combinations of GROUP BY and/or PARTITION BY, plus ROW_OVER to roll up the rows. I've tried all combinations I can think of with no success. There must be TSQL query to do this without resorting to cursors. Can anyone help? Thanks in advance.

CodePudding user response：

select   min(tag)               as tag
        ,string_agg(name, ', ') as name
        ,sum(size)              as size
from     t
group by rank

Fiddle

CodePudding user response：

Looking through the requirements, I seems we are grouping by the rank column. Additionally, some of the fields are determined by row with an A for the tag value, which isn't always present.

With that in mind, we need to start by selecting a "seed" row for each rank. The easiest way to do this is using the row_number() function to sort within each rank and then pick the first row:

SELECT *
FROM (
    SELECT *, row_number() over (partition by rank order by case when tag = 'A' then 0 when coalesce(tag,'') = '' then 1 else 2 end, tag) as rn
    FROM [table]
) t
WHERE rn = 1

Now we know which row to use as the base for each rank, but that's not enough. We also need to know about other name values for the same rank. We can find this if we take the first query and JOIN it back to the same table based on rank again, and then use GROUP BY STRING_AGG() to get our final fields. For convenience, I'll use the first query as a CTE:

WITH base_rows AS (
    SELECT rank, size, tag, seq, rn
    FROM (
        SELECT *, row_number() over (partition by rank order by case when tag = 'A' then 0 when coalesce(tag,'') = '' then 1 else 2 end, tag) as rn
        FROM [table]
    ) t
    WHERE rn = 1
)
SELECT b.tag, string_agg(t.name,',') names, b.size
FROM base_rows b
INNER JOIN [table] t ON t.rank = b.rank
GROUP BY b.rank, b.tag, b.size, b.seq
ORDER BY b.Seq

See it work here:

https://dbfiddle.uk/jt7m_EpK