July 27, 2015 at 3:16 pm
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed
July 27, 2015 at 5:36 pm
brody.rathman (7/27/2015)
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed
It's very helpful to those trying to help you out if you help us first, by supplying the SQL statements necessary to create tables with the relevant structures and inserting sample data. See the first link in my signature for how to do this.
For what you gave us, this would be:
DECLARE @test-2 TABLE (RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED, subid INTEGER, [text] VARCHAR(250));
INSERT INTO @test-2 (subid, text)
VALUES (1,'processing A records' ),
(2,'1000 records processing' ),
(3,'importing A records' ),
(4,'1000 records processed' ),
(1,'processing B records' ),
(2,'500 records processing' ),
(3,'importing B records' ),
(4,'1000 records processed' );
Now, to solve your issue:
WITH cte AS
(
SELECT RowID, subid, [text], ROWID - subid AS Grp
FROM @test-2
), cte2 AS
(
SELECT DISTINCT Grp
FROM cte
), cte3 AS
(
SELECT *, RANK() OVER (ORDER BY Grp) AS ID
FROM cte2
)
SELECT cte3.ID,
subid,
[text]
FROM cte
JOIN cte3 ON cte.Grp = cte3.Grp
ORDER BY cte3.ID, subid;
What this code does is to create "data islands", where we use the sequential number RowID, and subtract from it the group number. This will, for each group, give us a constant value (in the Grp column).
Next, we need to get each of these values in a distinct list, and then utilize the RANK() function to assign them a sequential number. This gives us the ID value.
Finally, we take the data islands, and join it to the CTE (common table expression) that gives us the ID value, to show all of them.
In the above example, the results are:
ID subid text
-------------------- ----------- ------------------------
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply