Reputation: 3362
Row Number Based on Conditions
declare @delimiter nvarchar(2) = char(10);
declare @objectName sysname = 'dbo.table_1';
-----------------------------------------------------------------------------------------------
with CTE as (
select
0 as line_number
, @objectName as [object_name]
, ltrim(rtrim(object_definition( object_id(@objectName)))) as def
, convert(nvarchar(max), N'') as line
union all
select
line_number + 1
, @objectName as [object_name]
, substring(def, charindex(@delimiter, def) + len(@delimiter), len(def) - (charindex(@delimiter, def)))
, left(def, charindex(@delimiter, def)) as line
from
CTE
where
charindex(@delimiter, def) <> 0
)
select
--a.line_number
ROW_NUMBER() OVER (PARTITION BY a.[object_name] order by a.[line_number]) as [new_line_num]
,a.[object_name]
-----
,a.line
-----
,case
when a.line like '%openquery%' then 'OPENQUERY'
else ''
end as [open_query_label]
-----
,case
when a.line like '%openquery%' then ROW_NUMBER () OVER(partition by a.[object_name] Order by (select 1) )
else 0
end as [open_query_group]
from
CTE as a
where
a.line_number >= 1
OPTION (MAXRECURSION 0);
This query produces the following output:
new_line_num object_name line open_query_label open_query_group
147 dbo.table_1 ,max(case when quest...) 0
148 dbo.table_1 ,max(case when quest...) 0
149 dbo.table_1 ,max(case when quest...) 0
150 dbo.table_1 ,max(case when quest...) 0
My ultimate goal is to grab all of OPENQUERY parts of each definition.
The way I'm going to do this is by labeling the line where the OPENQUERY starts and then grabbing the next 15 lines. It won't be 100% accurate, but it will be pretty close.
I want the output to look like this:
new_line_num object_name line open_query_label open_query_group
142 dbo.table_name ,max(case when... 0
153 dbo.table_name OPENQUERY([whatever OPENQUERY 1
154 dbo.table_name 'select 2
After open_query_group takes a value of 15, I'd like it to go back to 0 unless there is another OPENQUERY in the SQL statement. When that happens, I'd like the OPEN_QUERY_GROUP number to start back at 1.
Any suggestions on how to manipulate the ROW_NUMBER function?
Upvotes: 0
Views: 81
Answers (1)
Reputation: 1440
Here is a partial solution, I cant use your CTE to do anything, so I made some dummy data.
WITH cte
AS
(SELECT
*
FROM (VALUES
(1, ''), (2, ''), (3, 'OPENQUERY'), (4, ''), (5, ''), (6, ''), (7, ''), (8, ''), (9, ''), (10, ''), (11, 'OPENQUERY')
, (12, ''), (13, ''), (14, ''), (15, ''), (16, ''), (17, ''), (18, ''), (19, ''), (20, ''), (21, ''), (22, '')
, (23, ''), (24, ''), (25, ''), (26, ''), (27, ''), (28, ''), (29, ''), (30, ''), (31, ''), (32, ''), (33, '')
, (34, ''), (35, ''), (36, 'OPENQUERY'), (37, ''), (38, ''), (39, ''), (40, ''), (41, ''), (41, '')) a (new_line_num, open_query_label))
The trick is to first create one group for each instance of OPENQUERY.
I do this by toing an aggregate sum, setting the row value to1 where there is an OPENQUERY and to 0 else. This makes a distinct set of groups, 1 pr OPENQUERY (plus anything that comes before the first openquery)
SELECT
new_line_num
,open_query_label
,SUM(IIF(open_query_label = 'OPENQUERY', 1, 0)) OVER (ORDER BY new_line_num) grp
FROM cte
This gives us this (partial result)
+--------------+------------------+-----+
| new_line_num | open_query_label | grp |
+--------------+------------------+-----+
| 1 | | 0 |
| 2 | | 0 |
| 3 | OPENQUERY | 1 |
| 4 | | 1 |
| 5 | | 1 |
| 6 | | 1 |
| 7 | | 1 |
| 8 | | 1 |
| 9 | | 1 |
| 10 | | 1 |
| 11 | OPENQUERY | 2 |
| 12 | | 2 |
| 13 | | 2 |
| 14 | | 2 |
+--------------+------------------+-----+
Now i pack this as CTE2, and then I just use row_number to get the groups:
WITH cte
AS
(SELECT
*
FROM (VALUES
(1, ''), (2, ''), (3, 'OPENQUERY'), (4, ''), (5, ''), (6, ''), (7, ''), (8, ''), (9, ''), (10, ''), (11, 'OPENQUERY')
, (12, ''), (13, ''), (14, ''), (15, ''), (16, ''), (17, ''), (18, ''), (19, ''), (20, ''), (21, ''), (22, '')
, (23, ''), (24, ''), (25, ''), (26, ''), (27, ''), (28, ''), (29, ''), (30, ''), (31, ''), (32, ''), (33, '')
, (34, ''), (35, ''), (36, 'OPENQUERY'), (37, ''), (38, ''), (39, ''), (40, ''), (41, ''), (41, '')) a (new_line_num, open_query_label)),
cte2
AS
(SELECT
new_line_num
,open_query_label
,SUM(IIF(open_query_label = 'OPENQUERY', 1, 0)) OVER (ORDER BY new_line_num) grp
FROM cte)
SELECT
new_line_num
,open_query_label
,ROW_NUMBER() OVER (PARTITION BY grp ORDER BY new_line_num) open_query_group
FROM cte2
Partial Result:
+--------------+------------------+------------------+
| new_line_num | open_query_label | open_query_group |
+--------------+------------------+------------------+
| 1 | | 1 |
| 2 | | 2 |
| 3 | OPENQUERY | 1 |
| 4 | | 2 |
| 5 | | 3 |
| 6 | | 4 |
| 7 | | 5 |
| 8 | | 6 |
| 9 | | 7 |
| 10 | | 8 |
| 11 | OPENQUERY | 1 |
| 12 | | 2 |
+--------------+------------------+------------------+
Finally we want the first to rows to start at 0 in stead of 1, and for the open_query_group to roll around from 15 to 0
SELECT
new_line_num
,open_query_label
,(ROW_NUMBER() OVER (PARTITION BY grp ORDER BY new_line_num)
- iif(grp=0,1,0))%16
open_query_group
FROM cte2
This treats the first grp (0) specially, and uses modulus 16 to make the row number roll over from 15 to 0.
The final result:
+--------------+------------------+------------------+
| new_line_num | open_query_label | open_query_group |
+--------------+------------------+------------------+
| 1 | | 0 |
| 2 | | 1 |
| 3 | OPENQUERY | 1 |
| 4 | | 2 |
| 5 | | 3 |
| 6 | | 4 |
| 7 | | 5 |
| 8 | | 6 |
| 9 | | 7 |
| 10 | | 8 |
| 11 | OPENQUERY | 1 |
| 12 | | 2 |
| 13 | | 3 |
| 14 | | 4 |
| 15 | | 5 |
| 16 | | 6 |
| 17 | | 7 |
| 18 | | 8 |
| 19 | | 9 |
| 20 | | 10 |
| 21 | | 11 |
| 22 | | 12 |
| 23 | | 13 |
| 24 | | 14 |
| 25 | | 15 |
| 26 | | 0 |
| 27 | | 1 |
| 28 | | 2 |
| 29 | | 3 |
| 30 | | 4 |
| 31 | | 5 |
| 32 | | 6 |
| 33 | | 7 |
| 34 | | 8 |
| 35 | | 9 |
| 36 | OPENQUERY | 1 |
| 37 | | 2 |
| 38 | | 3 |
| 39 | | 4 |
| 40 | | 5 |
| 41 | | 6 |
| 41 | | 7 |
+--------------+------------------+------------------+
Upvotes: 1
Related Questions
- Specific Condition of a column based ROW_NUMBER in SQL Server
- T-SQL Row_Number Condition/Case
- How to make Row_number() based on condition?
- Condition evaluation and applying row numbers in a table
- How to modify Row_Number syntax in SQL so that row numbers are assigned based on a condition?
- row counter with condition in two different columns
- SQL row_number() with conditions
- Getting ROW_NUMBER to repeat if field meets a condition
- Row_number() that depends on condition
- row number based on field data