CODEWITHSUNDEEP
sqlsql-server-2008
R. Salehi
R. Salehi

Reputation: 183

Create query for specific summarizing in SQL

I have a table in SQL named 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

I want to convert this table to IndexReport table for using it in my reports in VB.Net like below:

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

I can do this in VB but I am to do that in SQL with query or SP directly in SQL.

Would you please help me?

Upvotes: 2

Views: 55

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

First, you want to group each PartName with continuous Index. You can do that using ROW_NUMBER. And then after the grouping, find the MIN and MAX of each group to be used for concatenation. When MIN and MAX is not the same, format the string so that it follows the pattern: '(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

ONLINE DEMO

Upvotes: 2

Related Questions