Создание запроса для конкретного суммирования в SQL

У меня есть таблица в SQL с именем SupplyIndex: 

ID          PartName Index
----------- -------- -----------
1           C        1
2           C        2
3           C        3
4           C        10
5           C        20
6           C        21
7           B        5
8           B        6
9           B        7
10          B        8

Я хочу преобразовать эту таблицу в таблицу IndexReport для использования ее в моих отчетах в VB.Net как показано ниже: 

PartName Index                      
-------- --------------------------
C        (1 to 3), 10, (20 to 21)   
B        (5 to 8)

Я могу сделать это в VB, но я должен сделать это в SQL с запросом или SP непосредственно в SQL.

Не могли бы вы помочь мне?

1 2
sql sql-server-2008

1 ответ:

Во-первых, вы хотите сгруппировать каждый PartName с непрерывным Index. Вы можете сделать это с помощью ROW_NUMBER. А затем после группировки Найдите MIN и MAX каждой группы, которые будут использоваться для конкатенации. Если MIN и MAX не одно и то же, отформатируйте строку так, чтобы она следовала шаблону: '(MIN to MAX)'.

WITH Cte AS(
    SELECT *,
        grp = [Index] - ROW_NUMBER() OVER(PARTITION BY PartName ORDER BY [Index])           
    FROM SupplyIndex
)
SELECT
    s.PartName,
    x.[Index],
    s.Qty
FROM (
    SELECT 
        PartName, COUNT(*) AS Qty
    FROM SupplyIndex
    GROUP BY PartName
) s
CROSS APPLY(
    SELECT STUFF((
        SELECT ', ' +
            CASE
                WHEN MIN(c.[Index]) = MAX(c.[Index]) THEN CONVERT(VARCHAR(10), MIN(c.[Index]))
                ELSE '(' + CONVERT(VARCHAR(10), MIN(c.[Index])) + ' to ' + CONVERT(VARCHAR(10), MAX(c.[Index])) + ')'
            END
        FROM Cte c
        WHERE c.PartName = s.PartName
        GROUP BY c.PartName, c.grp
        FOR XML PATH('')
    ), 1, 2, '') AS [Index]
)x
ORDER BY s.Qty DESC

ОНЛАЙН-ДЕМОНСТРАЦИЯ

1