March 13, 2010 at 12:29 am
Hi all,
We have a huge database sets, where members are given continuous running number from 1 to 5000+. these members might be member of any of the group, like grp1, grp2, grp3 etc. so the list look like
mem-ID grp-ID
1 ---- 2
2 ---- 2
3 ---- 1
4 ---- 1
5 ---- 1
6 ---- 3
7 ---- 1
8 ---- 1
9 ---- 1
10 ---- 3
11 ---- 3
12 ---- 3
13 ---- 2
14 ---- 2
15 ---- 2
Now, how to get the members from to list for each group something like this?
group1 : 3 to 5, 7 to 9
group2: 1 to 2, 13 to 15
group3: 6, (or 6 to 6), 10 to 12
How can I query the DB to get the list in this way? please help.
March 13, 2010 at 1:25 am
Setup:
DROP TABLE #Data;
GO
CREATE TABLE #Data
(
mem_id INTEGER NOT NULL PRIMARY KEY,
group_id INTEGER NOT NULL,
);
GO
INSERT #Data (mem_id, group_id) VALUES (01, 2);
INSERT #Data (mem_id, group_id) VALUES (02, 2);
INSERT #Data (mem_id, group_id) VALUES (03, 1);
INSERT #Data (mem_id, group_id) VALUES (04, 1);
INSERT #Data (mem_id, group_id) VALUES (05, 1);
INSERT #Data (mem_id, group_id) VALUES (06, 3);
INSERT #Data (mem_id, group_id) VALUES (07, 1);
INSERT #Data (mem_id, group_id) VALUES (08, 1);
INSERT #Data (mem_id, group_id) VALUES (09, 1);
INSERT #Data (mem_id, group_id) VALUES (10, 3);
INSERT #Data (mem_id, group_id) VALUES (11, 3);
INSERT #Data (mem_id, group_id) VALUES (12, 3);
INSERT #Data (mem_id, group_id) VALUES (13, 2);
INSERT #Data (mem_id, group_id) VALUES (14, 2);
INSERT #Data (mem_id, group_id) VALUES (15, 2);
Solution:
WITH Sequenced
AS (
SELECT D.mem_id,
D.group_id,
sequence =
RANK() OVER (ORDER BY D.mem_id) -
RANK() OVER (PARTITION BY D.group_id ORDER BY D.mem_id)
FROM #Data D
)
SELECT S1.group_id,
range_list = STUFF(iTVF.range, 1, 2, SPACE(0))
FROM (
SELECT group_id
FROM Sequenced
GROUP BY
group_id
) S1
CROSS
APPLY (
SELECT ', ' +
CASE
WHEN MIN(S2.mem_id) < MAX(S2.mem_id)
THEN CONVERT(VARCHAR(20), MIN(S2.mem_id)) +
' to ' +
CONVERT(VARCHAR(20), MAX(S2.mem_id))
ELSE CONVERT(VARCHAR(20), MIN(S2.mem_id))
END
FROM Sequenced S2
WHERE S2.group_id = S1.group_id
GROUP BY
S2.group_id,
S2.sequence
FOR XML PATH('')
) iTVF (range);
Output:
group_id range_list
1 3 to 5, 7 to 9
2 1 to 2, 13 to 15
3 6, 10 to 12
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply