我有一个包含这样结果的表格:

EMP_ID  | Boss_ID | Boss_Name | Specialty

  1001     001      John             sql
  1001     001      John             c#
  1002     002      James            c++
  1002     003      Sarah            sql
  1002     003      Sarah            python
  1003     004      Jesse            networking

员工可以有多个老板，老板可以有多个专长。

我需要动态地对数据进行透视，以便每个员工都有一行。我需要显示员工、他们拥有的所有老板以及他们的第 1 行专业。

我可以使用动态枢轴并显示多个老板部分，但我不确定如何在他们的名字旁边显示他们的专长。

这是我的:

   DECLARE @cols        AS NVARCHAR(max)
          ,@query       AS NVARCHAR(max)

   select t.EMP_ID 
         ,t.Boss_Name
         ,t.Boss_ID
         ,t.Specialty
         ,'BOSS' + '_' + cast(ROW_NUMBER() OVER (PARTITION BY t.EMP_ID ORDER BY t.BOSS_ID asc) AS VARCHAR) AS b_rn
         ,'SPEC' + '_' + cast(ROW_NUMBER() OVER (PARTITION BY t.Specialty ORDER BY t.BOSS_ID asc) AS VARCHAR) AS spec_rn
   INTO #work 
   from #testing t

SELECT @cols = STUFF(
                  (SELECT DISTINCT TOP 100 PERCENT '],[' + w.b_rn  
                   FROM #work w
                   ORDER BY '],[' + w.b_rn
                   FOR XML PATH('') 
                  ), 1, 2, ''
                ) + ']'

PRINT @cols

SET @query = N' SELECT EMP_ID,' + @cols + N' 
            INTO ##work_results FROM 
                 (
                  SELECT EMP_ID
                        ,Boss_Name
                        ,b_rn 

                  FROM #work
                  ) AS sourcetable 
PIVOT
(
 max(Boss_Name) for b_rn IN ('+ @cols + N') 
 ) AS pivot_table'


execute(@query)

select * from ##work_results

这给了我:

EMP_ID  |   BOSS_1  |   BOSS_2  |   BOSS_3
1001        John       John          NULL
1002        James      Sarah         Sarah
1003        Jesse      NULL          NULL

我真正想要的是这样的结果:

  EMP_ID | Boss_1 | Specialty_1 | Boss_2 | Specialty_1   

  1001     John     sql 
  1002     James    c++           Sarah      sql
  1003     Jesse    networking

我看过其他帖子，但它们并不是我想要的。

任何帮助都会很棒。

谢谢!

最佳答案

你可以这样走:

诀窍是连接您的值。因此只有一列，数据透视没有问题...

DECLARE @tbl TABLE(EMP_ID INT,Boss_ID INT,Boss_Name VARCHAR(100),Specialty VARCHAR(100));
INSERT INTO @tbl VALUES
 (1001,001,'John','sql')
,(1001,001,'John','c#')
,(1002,002,'James','c++')
,(1002,003,'Sarah','sql')
,(1002,003,'Sarah','python')
,(1003,004,'Jesse','networking');

SELECT p.*
FROM
(
    SELECT tbl.EMP_ID
          --If you need your Bosses in the given order you must add a sort crit to your original table!
          ,'Column_' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.EMP_ID ORDER BY tbl.EMP_ID) AS VARCHAR(MAX)) AS ColumnName
          ,tbl.Boss_Name + ' (' +  tbl.Specialty + ')' AS Concatenated
    FROM @tbl AS tbl
) AS ToBePivoted
PIVOT
(
    MIN(Concatenated) FOR ColumnName IN(Column_1,Column_2,Column_3 /*add maximum here*/)
) As p

结果

1001    John (sql)          John (c#)     NULL
1002    James (c++)         Sarah (sql)   Sarah (python)
1003    Jesse (networking)  NULL          NULL

如果您需要分隔值，另一个技巧是，以 XML 格式(但不是 XML 类型!)连接所有需要的列。

由于连接是 XML 格式的，因此很容易通过索引提取元素。

DECLARE @tbl TABLE(EMP_ID INT,Boss_ID INT,Boss_Name VARCHAR(100),Specialty VARCHAR(100));
INSERT INTO @tbl VALUES
 (1001,001,'John','sql')
,(1001,001,'John','c#')
,(1002,002,'James','c++')
,(1002,003,'Sarah','sql')
,(1002,003,'Sarah','python')
,(1003,004,'Jesse','networking');

SELECT p.EMP_ID

      ,CAST(Column_1 AS XML).value('x[1]','int') AS BossID_1 
      ,CAST(Column_1 AS XML).value('x[2]','varchar(max)') AS BossName_1 
      ,CAST(Column_1 AS XML).value('x[3]','varchar(max)') AS specialty_1 

      ,CAST(Column_2 AS XML).value('x[1]','int') AS BossID_2 
      ,CAST(Column_2 AS XML).value('x[2]','varchar(max)') AS BossName_2
      ,CAST(Column_2 AS XML).value('x[3]','varchar(max)') AS specialty_2 

      ,CAST(Column_3 AS XML).value('x[1]','int') AS BossID_3 
      ,CAST(Column_3 AS XML).value('x[2]','varchar(max)') AS BossName_3 
      ,CAST(Column_3 AS XML).value('x[3]','varchar(max)') AS specialty_3 
      /*Add a maximum here*/
FROM
(
    SELECT tbl.EMP_ID
          --If you need your Bosses in the given order you must add a sort crit to your original table!
          ,'Column_' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.EMP_ID ORDER BY tbl.EMP_ID) AS VARCHAR(MAX)) AS ColumnName
          ,'<x>' + CAST(tbl.Boss_ID AS VARCHAR(10)) + '</x><x>' +  tbl.Boss_Name + '</x><x>' +  tbl.Specialty + '</x>' AS ValuesAsXML
    FROM @tbl AS tbl
) AS ToBePivoted
PIVOT
(
    MIN(ValuesAsXML) FOR ColumnName IN(Column_1,Column_2,Column_3 /*add maximum here*/)
) As p

结果

1001    1   John    sql         1       John    c#      NULL    NULL    NULL
1002    2   James   c++         3       Sarah   sql     3       Sarah   python
1003    4   Jesse   networking  NULL    NULL    NULL    NULL    NULL    NULL

https://stackoverflow.com/questions/33551398/