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

Comments

Popular posts from this blog

การตั้งเวลาระหว่าง Server และ Client

วิธีตั้งค่า NTP บน Primary Domain Controller

Installation and Run Node.JS on IIS