T-SQL Recursive
Create Table
CREATE TABLE [dbo].[table1](
[main] [varchar](2) NULL,
[child] [varchar](2) NULL,
[qty] [int] NULL
) ON [PRIMARY]
Insert Data
INSERT INTO table1 VALUES ('A1','B1',1)
INSERT INTO table1 VALUES ('B1','C1',2)
INSERT INTO table1 VALUES ('B1','C2',3)
INSERT INTO table1 VALUES ('C1','D1',4)
SQL Query
with dt_main as (
select 1 as level, main, child, qty from table1 where main = 'first-level'
union all
select level + 1 as level, a.main, a.child, a.qty
from table1 a
inner join dt_main main on main.child = a.main
)
select * from dt_main
CREATE TABLE [dbo].[table1](
[main] [varchar](2) NULL,
[child] [varchar](2) NULL,
[qty] [int] NULL
) ON [PRIMARY]
Insert Data
INSERT INTO table1 VALUES ('A1','B1',1)
INSERT INTO table1 VALUES ('B1','C1',2)
INSERT INTO table1 VALUES ('B1','C2',3)
INSERT INTO table1 VALUES ('C1','D1',4)
SQL Query
with dt_main as (
select 1 as level, main, child, qty from table1 where main = 'first-level'
union all
select level + 1 as level, a.main, a.child, a.qty
from table1 a
inner join dt_main main on main.child = a.main
)
select * from dt_main
Comments
Post a Comment