各位大神,我想实现将人员的所有上级写入一行中分列显示,具体描述如下:
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语句呢?

解决方案 »

  1.   

    IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
    DROP TABLE #T
    GOCREATE 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',0GODECLARE @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 ASET @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)
      

  2.   


    大神,您这个语句的sql好像不是我想要的那个:
    我想要的结果是这个:
    请大神帮忙
      

  3.   


    大神,您这个语句的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 LEVEL'+CAST(LEVEL AS VARCHAR)
      FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @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)
      

  4.   


    大神,您这个语句的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 LEVEL'+CAST(LEVEL AS VARCHAR)
      FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @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)
    再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样:
      

  5.   

    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自定义函数,网上很多,把响应的函数方法名替换,字段替换即可
      

  6.   

    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有限层级的话,直接写也比较方便
      

  7.   


    大神,您这个语句的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 LEVEL'+CAST(LEVEL AS VARCHAR)
      FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @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)
    再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样: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 ASET @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)
      

  8.   

    大神,那怎么把这个结果写到一张表中呢,我还要再再这张表中对这些数据做处理大神,您这个语句的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 LEVEL'+CAST(LEVEL AS VARCHAR)
      FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @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)
    再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样: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 ASET @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)
      

  9.   


    大神,您这个语句的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 LEVEL'+CAST(LEVEL AS VARCHAR)
      FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @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)
    再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样: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 ASET @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)要么就把结果插入到一张全局临时表,然后对这张全局临时表进行操作,但是用完后要记得及时把这张表删了。把给@SQL赋值语句的最后一句改成下面的 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' INTO ##A FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME