SQL Server:一行多行，多列显示

Question

我发现了类似的问题，但没有一个解决方案，我已经能够使我的工作。我已经生疏了，在这上面卡住了。

我有一个跟踪员工职位的表。每个员工可以拥有任意数量的活动职位。我希望将每个员工显示为单个记录，其中他们在该记录中的所有职位跨越多个列。

看一下数据，没有员工拥有超过6个活跃职位，因为这是一次性查询，我应该能够处理这个数字。

所有的职位都有一个分配的序列号，但它们不是一个很方便的顺序-员工的非活动职位保留序列号。

现在我有了这样的内容:

    ID | Name | Title | Cat | Seq
    ------------------------------
    10 | John | Asst. | HR  | 13
    10 | John | Tutor | EDU | 17
    11 | Sue  | Mgr   | PA  | 6
    11 | Sue  | Adj.  | EDU | 7
    11 | Sue  | Tutor | EDU | 13
    ...
    11 | Sue  | Asst. | HR  | 22

但是我想让它看起来像这样:

    ID | Name | Title 1 | Cat 1 | Title 2 | Cat 2 | Title 3...| Title 6 | Cat 6
    ----------------------------------------------------------------------------
    10 | John | Asst.   | HR    | Tutor   | EDU   | NULL      | NULL    | NULL
    11 | Sue  | Mgr.    | PA    | Adj.    | EDU   | Tutor     | Asst.   | HR

请告知我还需要提供哪些信息

Answer 1

您可以使用row_number和pivots的组合来做到这一点。使用SQL Server 2012+，查询可能如下所示。虽然可能有更好的方法来达到同样的效果，但它应该可以完成工作。

select 
    id
    , name
    , max([Title 1]) as [Title 1]
    , max([Cat 1])   as [Cat 1]
    , max([Title 2]) as [Title 2]
    , max([Cat 2])   as [Cat 2]
    , max([Title 3]) as [Title 3]
    , max([Cat 3])   as [Cat 3]
    -- and so on for the remaining columns...
from (
    select 
       id, name, title, cat
       , t = concat('Title ',row_number() over (partition by id order by seq)) 
       , c = concat('Cat ',row_number() over (partition by id order by seq)) 
    from your_table
    ) src
pivot ( max(title) for t in ([title 1],[title 2],[title 3]) )pt
pivot ( max(cat)   for c in ([cat 1],[cat 2],[cat 3]) )pc
group by id, name;

为了节省空间，我只写了title/cat 1到3，但是你应该明白。

示例SQL Fiddle

样本结果:

| id | name | Title 1 | Cat 1 | Title 2 | Cat 2 | Title 3 |  Cat 3 |
|----|------|---------|-------|---------|-------|---------|--------|
| 10 | John |   Asst. |   HR  |   Tutor |   EDU |  (null) | (null) |
| 11 |  Sue |   Mgr   |   PA  |   Adj.  |   EDU |   Tutor |    EDU |