在sql server中将数据从多个记录转换为单个记录

Question

我需要将多个相似记录转换为单个记录。 最多可以有10行需要组合。 需要组合的每组行都具有相同的ID。行数据的值是不相关的（实际上将是一个GUID）。 数据看起来是这样的：

表A

ID C1 C2 C3 
ID1 x x  x 
ID1 y y  y 
ID2 y y  y 
ID2 x x  x 
ID2 y y  y 
ID2 y y  y 
ID3 x x  x 
ID3 y y  y 
ID3 y y  y 

我需要转换到这个结构，只有每个ID只能有一个记录。根据具有相同ID（约10）的记录数可以有N列。

表B

ID  C1  C2  C3  C1A  C2A  C3A  C1B  C2B  C3B 
ID1 x  x  x  y  y  y  null null null 
ID2 y  y  y  x  x  x  y  y  y 
ID3 x  x  x  y  y  y  y  y  y 

我不能修改表B中的。只需合并或插入它。
我正在使用SQL Server 2008 R2，表A的卷大约有一百万条记录。
任何帮助大大appriciated。

更新：添加真实的表格定义。

这里是表A创建脚本：

SET ANSI_NULLS ON 
    GO 

SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[IntervalPivotTable](
    [UID] [uniqueidentifier] NOT NULL, 
    [ServiceHash] [int] NULL, 
    [IntervalID] [nvarchar](50) NULL, 
    [IntervalTypeID] [nvarchar](50) NULL, 
    [IntervalGroupID] [nvarchar](50) NULL, 
    [DrivingConditionID] [nvarchar](50) NULL, 
CONSTRAINT [PK_IntervalPivotTable] PRIMARY KEY CLUSTERED 
(
    [UID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =  ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 

GO 

ALTER TABLE [dbo].[IntervalPivotTable] ADD CONSTRAINT [DF_IntervalPivotTable_UID] DEFAULT   (newid()) FOR [UID] 
GO 

下面是表B创建脚本： SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[PivotedIntervals](
    [ServiceHash] [int] NULL, 
    [IntervalID_0] [nvarchar](50) NULL, 
    [IntervalTypeID_0] [nvarchar](50) NULL, 
    [IntervalGroupID_0] [nvarchar](50) NULL, 
    [DrivingConditionID_0] [nvarchar](50) NULL, 
    [IntervalID_1] [nvarchar](50) NULL, 
    [IntervalTypeID_1] [nvarchar](50) NULL, 
    [IntervalGroupID_1] [nvarchar](50) NULL, 
    [DrivingConditionID_1] [nvarchar](50) NULL, 
    [IntervalID_2] [nvarchar](50) NULL, 
    [IntervalTypeID_2] [nvarchar](50) NULL, 
    [IntervalGroupID_2] [nvarchar](50) NULL, 
    [DrivingConditionID_2] [nvarchar](50) NULL, 
    [IntervalID_3] [nvarchar](50) NULL, 
    [IntervalTypeID_3] [nvarchar](50) NULL, 
    [IntervalGroupID_3] [nvarchar](50) NULL, 
    [DrivingConditionID_3] [nvarchar](50) NULL, 
    [IntervalID_4] [nvarchar](50) NULL, 
    [IntervalTypeID_4] [nvarchar](50) NULL, 
    [IntervalGroupID_4] [nvarchar](50) NULL, 
    [DrivingConditionID_4] [nvarchar](50) NULL, 
    [IntervalID_5] [nvarchar](50) NULL, 
    [IntervalTypeID_5] [nvarchar](50) NULL, 
    [IntervalGroupID_5] [nvarchar](50) NULL, 
    [DrivingConditionID_5] [nvarchar](50) NULL, 
    [IntervalID_6] [nvarchar](50) NULL, 
    [IntervalTypeID_6] [nvarchar](50) NULL, 
    [IntervalGroupID_6] [nvarchar](50) NULL, 
    [DrivingConditionID_6] [nvarchar](50) NULL, 
    [IntervalID_7] [nvarchar](50) NULL, 
    [IntervalTypeID_7] [nvarchar](50) NULL, 
    [IntervalGroupID_7] [nvarchar](50) NULL, 
    [DrivingConditionID_7] [nvarchar](50) NULL, 
    [IntervalID_8] [nvarchar](50) NULL, 
    [IntervalTypeID_8] [nvarchar](50) NULL, 
    [IntervalGroupID_8] [nvarchar](50) NULL, 
    [DrivingConditionID_8] [nvarchar](50) NULL, 
    [IntervalID_9] [nvarchar](50) NULL, 
    [IntervalTypeID_9] [nvarchar](50) NULL, 
    [IntervalGroupID_9] [nvarchar](50) NULL, 
    [DrivingConditionID_9] [nvarchar](50) NULL, 
    [IntervalID_10] [nvarchar](50) NULL, 
    [IntervalTypeID_10] [nvarchar](50) NULL, 
    [IntervalGroupID_10] [nvarchar](50) NULL, 
    [DrivingConditionID_10] [nvarchar](50) NULL 
) ON [PRIMARY] 

GO 

Answer 1

你可能想尝试unpivot/pivot组合。逆透视变换表A与三列只，ID，ColumnID的和GUID行，例如

ID1, C1_0, x 
ID1, C2_0, x 
ID1, C3_0, x 
ID1, C1_1, x 
ID1, C2_1, x 
ID1, C3_1, x 

添加唯一的号码以每个ID /列组合（ROW_NUMBER超过（...）的一部分），并执行对于每个在TableA中排。枢轴将它们转换成单排通过ID：

select * 
from 
(
    select id, 
     code 
     + '_' 
     + convert(varchar(10), ColumnID) - 1 ColumnID, 
     guid 
    from 
    (
    select TableA.*, 
      row_number() over (partition by ID 
           order by TableA_PK) - 1 ColumnID 
     from TableA 
) 
    unpivot 
    (
    guid for code in (c1, c2, c3) 
) as u 
) UnpivotedTable 
pivot 
(
    min(guid) 
    for columnid in ([c1_0], [c2_0], [c3_0], [c1_1], [c2_1], [c3_1]) 
) PivotedTable 

不要分钟（GUID）的存在感到震惊。这是因为枢轴坚持聚合函数。由于每个ID/ColumnID组合只有一个GUID，因此不会有缺失值。要验证，用count（guid）替换min（guid）并检查大于1的值。

UPDATE：为避免混合来自同一ID的不同行，我必须更改row_number（）表A的关键。如果表中没有PK，应变换的输入，以使其包括ROW_NUMBER（）作为一个代理主键：

(
     select TableA.*, row_number() over (order by ID) TableA_PK 
     from TableA 
) TableAWithAPrimaryKey 

和使用，而不是原来的表派生表。

更新2：

我的错误是在放置unpivot的选择ROW_NUMBER（）。显然，在转换之前必须提取ID的行号。这是一个使用原始表的关键点：

select * 
from 
(
    select ServiceHash, 
     code 
     + '_' 
     + convert(varchar(10), ColumnID) ColumnID, 
     guid 
    from 
    (
    select IntervalPivotTable.*, 
      row_number() over (partition by ServiceHash 
          order by UID) - 1 ColumnID 
     from IntervalPivotTable 
) a 
    unpivot 
    (
    guid for code in 
    (
     IntervalID, 
     IntervalTypeID, 
     IntervalGroupID, 
     DrivingConditionID 
    ) 
) as u 
) UnpivotedTable 
pivot 
(
    min(guid) 
    for columnid in 
    (
    IntervalID_0, 
    IntervalTypeID_0, 
    IntervalGroupID_0, 
    DrivingConditionID_0, 
    IntervalID_1, 
    IntervalTypeID_1, 
    IntervalGroupID_1, 
    DrivingConditionID_1, 
    IntervalID_2, 
    IntervalTypeID_2, 
    IntervalGroupID_2, 
    DrivingConditionID_2 
-- Continue list of pivoted columns up to _10 here 
) 
) PivotedTable