34,588
社区成员
发帖
与我相关
我的任务
分享将人员表中的上级写到一行中分列显示
各位大神，我想实现将人员的所有上级写入一行中分列显示，具体描述如下：
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语句呢？
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 2019-12-16
- 打赏
- 举报
那如果想把这个结果以视图的方式展示呢？写成一张视图可以吗？
小白619 2019-11-22
- 打赏
- 举报
大神，那怎么把这个结果写到一张表中呢，我还要再再这张表中对这些数据做处理
大神，您这个语句的sql好像不是我想要的那个：
我想要的结果是这个：
请大神帮忙[/quote]
昨天那个有些问题，你再试试下面的
，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：
[/quote]
我想要的结果是这个：
请大神帮忙[/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)
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)
RINK_1 2019-11-22
- 打赏
- 举报
大神，您这个语句的sql好像不是我想要的那个：
我想要的结果是这个：
请大神帮忙[/quote]
昨天那个有些问题，你再试试下面的
，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：
[/quote]
我想要的结果是这个：
请大神帮忙[/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)
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)
RINK_1 2019-11-20
- 打赏
- 举报
大神，您这个语句的sql好像不是我想要的那个：
我想要的结果是这个：
请大神帮忙[/quote]
昨天那个有些问题，你再试试下面的
我想要的结果是这个：
请大神帮忙[/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
- 打赏
- 举报
大神，您这个语句的sql好像不是我想要的那个：
我想要的结果是这个：
请大神帮忙
我想要的结果是这个：
请大神帮忙RINK_1 2019-11-20
- 打赏
- 举报
大神，您这个语句的sql好像不是我想要的那个：
我想要的结果是这个：
请大神帮忙[/quote]
昨天那个有些问题，你再试试下面的
，请问可以将每个上级的ID也添加到里面，分列显示，例如这样：
[/quote]
我想要的结果是这个：
请大神帮忙[/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)
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 行受影响)
小白619 2019-11-20
- 打赏
- 举报
大神，您这个语句的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 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-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)
