将人员表中的上级写到一行中分列显示

2019-11-19 10:00:34 195 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

各位大神，我想实现将人员的所有上级写入一行中分列显示，具体描述如下：
id name manageid(上级ID)
1 A 2
2 A1 3
3 A2 4
4 A3 0 --无上级
7 B 8
8 B1 4
4 A3 0 --无上级

条件是：人员.manageid=该人员上级的id

想实现人员有N个上级，则自动添加N列来显示上级：
id name shangji1 shangji2 shangji3
1 A A1 A2 A3
7 B B1 A3 null

请问怎么写sql语句呢？

...全文

小白619

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

小白619 2019-12-16

打赏
举报

那如果想把这个结果以视图的方式展示呢？写成一张视图可以吗？

小白619 2019-11-22

打赏
举报

大神，那怎么把这个结果写到一张表中呢，我还要再再这张表中对这些数据做处理

引用 9 楼 RINK_1 的回复:

[quote=引用 6 楼小白619 的回复:] [quote=引用 4 楼 RINK_1 的回复:] [quote=引用 3 楼小白619 的回复:] [quote=引用 2 楼 RINK_1 的回复:]



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙[/quote] 昨天那个有些问题，你再试试下面的



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote] 再次感谢大神

，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：

[/quote]



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
 +',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
 FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote]

RINK_1 2019-11-22

打赏
举报

引用 10 楼小白619 的回复:

大神，那怎么把这个结果写到一张表中呢，我还要再再这张表中对这些数据做处理 [quote=引用 9 楼 RINK_1 的回复:] [quote=引用 6 楼小白619 的回复:] [quote=引用 4 楼 RINK_1 的回复:] [quote=引用 3 楼小白619 的回复:] [quote=引用 2 楼 RINK_1 的回复:]



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙[/quote] 昨天那个有些问题，你再试试下面的



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote] 再次感谢大神

，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：

[/quote]



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
 +',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
 FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote][/quote] 要么就把结果插入到一张全局临时表，然后对这张全局临时表进行操作，但是用完后要记得及时把这张表删了。把给@SQL赋值语句的最后一句改成下面的 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' INTO ##A FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME

RINK_1 2019-11-20

打赏
举报

引用 3 楼小白619 的回复:

[quote=引用 2 楼 RINK_1 的回复:]



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙[/quote] 昨天那个有些问题，你再试试下面的



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

小白619 2019-11-20

打赏
举报

引用 2 楼 RINK_1 的回复:



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙

RINK_1 2019-11-20

打赏
举报

引用 6 楼小白619 的回复:

[quote=引用 4 楼 RINK_1 的回复:] [quote=引用 3 楼小白619 的回复:] [quote=引用 2 楼 RINK_1 的回复:]



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙[/quote] 昨天那个有些问题，你再试试下面的



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote] 再次感谢大神

，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：

[/quote]



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
 +',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
 FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

文盲老顾 2019-11-20

打赏
举报

with t as (
	select 1 as id,'A' as n,2 as pid
	union all
	select 2,'A1',3
	union all
	select 3,'A2',4
	union all
	select 4,'A3',0
	union all
	select 7,'B',8
	union all
	select 8,'B1',4

),t1 as (
	select *,convert(varchar(4),'') as lv1,convert(varchar(4),'') as lv2,convert(varchar(4),'') as lv3,convert(varchar(4),'') as lv4 from t where pid=0
	union all
	select t.*,convert(varchar(4),t1.n),convert(varchar(4),lv1),convert(varchar(4),lv2),convert(varchar(4),lv3) from t,t1 where t.pid=t1.id
)
select * from t1

有限层级的话，直接写也比较方便

文盲老顾 2019-11-20

打赏
举报

with t as (
	select 1 as id,'A' as n,2 as pid
	union all
	select 2,'A1',3
	union all
	select 3,'A2',4
	union all
	select 4,'A3',0
	union all
	select 7,'B',8
	union all
	select 8,'B1',4

),t1 as (
	select *,convert(varchar(max),'') as p from t where pid=0
	union all
	select t.*,convert(varchar(max),t1.n+(case when t1.p='' then '' else ','+t1.p end)) from t,t1 where t.pid=t1.id
)
select id,n,isnull([1],'') as lv1,isnull([2],'') as lv2,isnull([3],'') as lv3,isnull([4],'') as lv4 from t1 a
cross apply (
	select id,convert(varchar(5),value) as value from master.dbo.splitstr(p,',')
) b
pivot (
	max(value) for b.id in ([1],[2],[3],[4])
) p


id          n    lv1   lv2   lv3   lv4
----------- ---- ----- ----- ----- -----
1           A    A1    A2    A3    
2           A1   A2    A3          
3           A2   A3                
4           A3                     
7           B    B1    A3          
8           B1   A3                

(6 行受影响)

类似split的sql自定义函数，网上很多，把响应的函数方法名替换，字段替换即可

小白619 2019-11-20

打赏
举报

引用 4 楼 RINK_1 的回复:

[quote=引用 3 楼小白619 的回复:] [quote=引用 2 楼 RINK_1 的回复:]



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)

大神，您这个语句的sql好像不是我想要的那个：

我想要的结果是这个：

请大神帮忙[/quote] 昨天那个有些问题，你再试试下面的



DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1 
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)


 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
 WHERE MANAGERID<>0
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'

EXEC(@SQL)

[/quote] 再次感谢大神

，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：

砸死牛顿的苹果 2019-11-19

打赏
举报

大概思路是递归+拼接

RINK_1 2019-11-19

打赏
举报



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT,
 NAME VARCHAR(10),
 MANAGERID INT)

INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0

GO

DECLARE @SQL VARCHAR(MAX)

;WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)

 SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
  FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS A

SET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),

CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
 UNION ALL
 SELECT A.*,B.GROUP_ID,B.LEVEL+1
 FROM CTE_1 A
 JOIN CTE_2 B ON B.MANAGERID=A.ID)
 
 SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'

EXEC(@SQL)