代码之家 › 专栏 › 技术社区 › Rob Truxal

转换和扩展列数据集T-SQL

transpose tsql database sql-server sql

Rob Truxal · 技术社区 · 6 年前

我有一个SQL表,它看起来像我在Excel中创建的以下示例:

以下是我期望的结果。我需要将上面的数据转换为两列列表:

为了防止需要更多的信息,下面是第一张图片,其中的注释描述了我使用行的原因:

3 回复 | 直到 6 年前

Avi 6 年前

您可能希望使用UNPIVOT获取预期的结果。

create table fruits (fruiteater varchar(100), Fruit1 varchar(100), Fruit2 varchar(100), 
Fruit3 varchar(100), Fruit4 varchar(100), Fruit5 varchar(100), Fruit6 varchar(100))  

insert into fruits values ('Aaron', 'Orange', 'Pear', 'Apple', NULL, NULL, NULL) 
,('Bob', 'Apple', 'Orange', 'Blueberries', 'Peach', 'Mango', 'Banana') 
,('Carter', 'Pear', 'Orange', 'Apple', NULL, NULL, NULL) 
,('David', 'Blueberries', NULL, NULL, NULL, NULL, NULL) 
,('Earnest', 'Mango', 'Orange', 'Pear', 'Apple','Blueberries', NULL) 
,('Frank', 'Raspberries', NULL, NULL, NULL, NULL, NULL)

由于所有这些结果都将在一个列中,因此在执行UNPIVOT时,必须注意数据类型和长度,因此我确保所有列都是相同的数据类型。

select fruiteater, Value as Fruit from fruits
unpivot 
(value for columnname in ([Fruit1], [Fruit2], [Fruit3], [Fruit4], [Fruit5],[Fruit6]))unpvt

输出:UNPIVOT将处理空值,因为它消除了这一点。

fruiteater  Fruit
Aaron   Orange
Aaron   Pear
Aaron   Apple
Bob     Apple
Bob     Orange
Bob     Blueberries
Bob     Peach
Bob     Mango
Bob     Banana
Carter  Pear
Carter  Orange
Carter  Apple
David   Blueberries
Earnest Mango
Earnest Orange
Earnest Pear
Earnest Apple
Earnest Blueberries
Frank   Raspberries

John Cappelletti 6 年前

UNPIVOT当然更具性能,但是如果您希望在不实际使用动态SQL的情况下动态地UNPIVOT数据,请考虑以下内容

例子

Select A.fruiteater
      ,C.*
 From  fruits A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('fruiteater','OtherColumnsToExclude')
             ) C

退换商品

fruiteater  Item    Value
Aaron       Fruit1  Orange
Aaron       Fruit2  Pear
Aaron       Fruit3  Apple
Bob         Fruit1  Apple
Bob         Fruit2  Orange
Bob         Fruit3  Blueberries
Bob         Fruit4  Peach
Bob         Fruit5  Mango
Bob         Fruit6  Banana
Carter      Fruit1  Pear
Carter      Fruit2  Orange
Carter      Fruit3  Apple
David       Fruit1  Blueberries
Earnest     Fruit1  Mango
Earnest     Fruit2  Orange
Earnest     Fruit3  Pear
Earnest     Fruit4  Apple
Earnest     Fruit5  Blueberries
Frank       Fruit1  Raspberries

Gordon Linoff 6 年前

我更喜欢横向连接( apply )为此目的。我认为语法更简单,它不做任何“魔术”如删除。 NULL 值,除非您明确希望它:

select f.fruiteater, v.fruit
from fruits f cross apply
     (values (fruit1),  (fruit2),  (fruit3), (fruit4), (fruit5), (fruit6)
     ) v(fruit)
where v.fruit is not null;

更重要的是, unpivot 是一种非常具体的语法。横向连接非常灵活和强大。利用它们来进行非食化是一种非常简单的方法来了解它们。