March 9, 2012 at 5:34 pm
Hi everyone,
I have a problem with writing a query that will group data based on an attribute in one column (ColC) and return an intervall.
The data looks like this:
ColA | ColB | ColC
0 3 A
3 5 A
5 7 B
7 8 A
8 10 C
10 12 C
12 15 A
15 16 A
16 17 A
17 18 B
and I want the output to be the following
ColA | ColB | ColC
0 5 A
5 7 B
7 8 A
8 12 C
12 17 A
17 18 B
I have been struggling with this for some time and not really making any progress.
Thanks for your help.
March 9, 2012 at 5:58 pm
Sample data :
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
CREATE TABLE #Temp
( iD INT IDENTITY(1,1)
,ColA INT
,ColB INT
,ColC CHAR(1)
)
INSERT INTO #Temp (ColA , ColB , ColC)
SELECT 0, 3 ,'A'
UNION ALL SELECT 3, 5 ,'A'
UNION ALL SELECT 5, 7 ,'B'
UNION ALL SELECT 7 ,8 ,'A'
UNION ALL SELECT 8 ,10 ,'C'
UNION ALL SELECT 10 ,12 ,'C'
UNION ALL SELECT 12 ,15 ,'A'
UNION ALL SELECT 15 ,16 ,'A'
UNION ALL SELECT 16 ,17 ,'A'
UNION ALL SELECT 17 ,18 ,'B'
Please look at how i set up the sample data; it took me 20 minutes to prepare it so that i can do a query for you. So , in future, please post your request in a format like that. Read this to learn more about Forum Etiquettes[/url]
Now the query:
; WITH CTE AS
(
SELECT T.ID , T.ColA , T.ColB , T.ColC
, RN2 = T.ID - ROW_NUMBER() OVER (PARTITION BY T.Colc ORDER BY T.ID)
FROM #Temp T
--ORDER BY T.iD , RN1
)
SELECT MIN(Base.ColA) ColA , MAX(Base.ColB) ColB, Base.ColC
FROM CTE Base
GROUP BY Base.ColC , Base.RN2
And the results from the query:
ColA ColB ColC
----------- ----------- ----
0 5 A
7 8 A
5 7 B
12 17 A
8 12 C
17 18 B
March 9, 2012 at 6:01 pm
note that i have added a column called "iD" in the set-up; without that, the query wil not work. THe rows must be uniquely-identifiable in an ordered fashion for my query to work. If you dont have iD column, the atleast you must have a column on which we can anchor the iD.
March 12, 2012 at 10:28 am
Thank you for helping with my problem. I found that the solution from SSCommitted was what I wanted.
Cheers
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply