基于单列旋转多列 [英] Pivoting Multiple Columns Based On a Single Column

问题描述

我尝试遵循本网站上的其他一些建议，但由于某种原因无法实现.

I tried following several other suggestions on this website but coulnd't make this work for some reason.

基本上我试图从这个任务表中获取:

Basically I'm trying to get from this table of Tasks:

| TaskID | ProjectID | TaskType | TaskDate1 | TaskDate2 |
---------------------------------------------------------
|      1 |         1 |    Type4 |  20130401 |  20130506 |
|      2 |         1 |    Type0 |  20130412 |  20130508 |
|      3 |         1 |    Type2 |  20130420 |  20130517 |

给那个:

| ProjectID | Type0Date1 | Type0Date2 | Type2Date1 | Type2Date2 | Type4Date1 | Type4Date2 |
---------------------------------------------------------
|         1 |   20130412 |   20130508 |   20130420 |   20130517 |   20130401 |   20130506 |

每个项目都必须有多个预定义任务(按类型标识)中的每一个.这些任务有很多属性，但我对 2 个日期感兴趣，最好将它们全部放在每个项目的一行中.

Each project has to have each of several predefined tasks (identified by their type). These tasks have a lot of properties but I'm interested in 2 dates and it would be nice to bring them all in one row per project.

我可以轻松地将它用于其中一列:PIVOT (MAX(TaskDate1) FOR TaskType IN ([Type0],[Type2],[Type4]))

I can easily pivot it for one of the columns with: PIVOT (MAX(TaskDate1) FOR TaskType IN ([Type0],[Type2],[Type4]))

我尝试复制 TaskType 列并为 TaskDate2 围绕它做另一个透视，但这不会给我一行.

I tried duplicating the TaskType column and doing another pivot around it for TaskDate2 but that won't give me a single row.

我也无法理解首先取消旋转表格如何让我到达我需要的位置，就像其他一些帖子中针对类似问题的建议一样.

I also can't wrap my head around how unpivoting the table first would get me where I need to be, as was the suggestion in some other posts for similar questions.

如果有一种可扩展的方式来做到这一点(如旋转更多列)，那就太好了.

If there is a scalable way of doing this (as in pivoting even more columns) it would be nice to know.

谢谢.

推荐答案

你可以得到想要的结果，但是因为你需要在多列(TaskDate1 和 TaskDate2) 我首先将两列反透视为多行.

You can get the desired result but since you need to PIVOT on multiple columns (TaskDate1 and TaskDate2) I would first unpivot the two columns into multiple rows.

由于您使用的是 SQL Server 2008+，您可以使用 UNPIVOT 函数或 CROSS APPLY 来转换多列.基本语法是:

Since you are using SQL Server 2008+, you can use either the UNPIVOT function or CROSS APPLY to convert the multiple columns. The basic syntax will be:

select projectid,
  col = TaskType+col,
  value
from yourtable
cross apply
(
  values 
    ('Date1', TaskDate1),
    ('Date2', TaskDate2)
) c(col, value)

参见SQL Fiddle with Demo.这会将您的数据转换为易于使用的格式:

See SQL Fiddle with Demo. This will convert your data into an easy to use format:

| PROJECTID |        COL |                        VALUE |
|-----------|------------|------------------------------|
|         1 | Type4Date1 | April, 01 2013 00:00:00+0000 |
|         1 | Type4Date2 |   May, 06 2013 00:00:00+0000 |
|         1 | Type0Date1 | April, 12 2013 00:00:00+0000 |
|         1 | Type0Date2 |   May, 08 2013 00:00:00+0000 |

一旦你有了这种格式的数据，你就可以应用 PIVOT 函数来获取每个 col 的 max(value):

Once you have the data in this format, then you can apply the PIVOT function to the get the max(value) for each col:

select projectId,
  Type0Date1, Type0Date2, 
  Type2Date1, Type2Date2,
  Type4Date1, Type4Date2
from
(
  select projectid,
    col = TaskType+col,
    value
  from yourtable
  cross apply
  (
    values 
      ('Date1', TaskDate1),
      ('Date2', TaskDate2)
  ) c(col, value)
) d
pivot
(
  max(value)
  for col in (Type0Date1, Type0Date2, Type2Date1, Type2Date2,
              Type4Date1, Type4Date2)
) piv;

参见SQL Fiddle with Demo.

如果每个 ProjectId 的值数量有限，上面的版本会很好用，但是如果每个项目的任务数量未知，则需要使用动态 SQL 来获取最终结果:

The above version will work great if you have a limited number of values per ProjectId, but if you have an unknown number of Tasks for each project, then you will need to use dynamic SQL to get the final result:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(TaskType+col) 
                    from yourtable
                    cross apply
                    (
                      select 'Date1', 1 union all
                      select 'Date2', 2
                    ) c(col, so)
                    group by col, tasktype, so
                    order by tasktype, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT projectid, ' + @cols + ' 
            from 
            (
              select projectid,
                col = TaskType+col,
                value
              from yourtable
              cross apply
              (
                values 
                  (''Date1'', TaskDate1),
                  (''Date2'', TaskDate2)
              ) c(col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query

参见SQL Fiddle with Demo.这些将给出结果:

See SQL Fiddle with Demo. These will give a result:

| PROJECTID |                   TYPE0DATE1 |                 TYPE0DATE2 |                   TYPE2DATE1 |                 TYPE2DATE2 |                   TYPE4DATE1 |                 TYPE4DATE2 |
|-----------|------------------------------|----------------------------|------------------------------|----------------------------|------------------------------|----------------------------|
|         1 | April, 12 2013 00:00:00+0000 | May, 08 2013 00:00:00+0000 | April, 20 2013 00:00:00+0000 | May, 17 2013 00:00:00+0000 | April, 01 2013 00:00:00+0000 | May, 06 2013 00:00:00+0000 |

这篇关于基于单列旋转多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！