排序PIVOT源的数据 [英] Sorting data for PIVOT source
本文介绍了排序PIVOT源的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
想象一个具有属性的表(键值)和具有手数的父表.
Imagine a table (key-value) with the Attributes and the parent table with Lots.
LotId SomeText
----------- --------
1 Hello
2 World
AttributeId LotId Val Kind
----------- ----------- -------- --------
1 1 Foo1 Kind1
2 1 Foo2 Kind2
3 2 Bar1 Kind1
4 2 Bar2 Kind2
5 2 Bar3 Kind3
我正在使用UNPIVOT-PIVOT操作来获取以下形式的数据:
I am using UNPIVOT - PIVOT operation to get the data in the form of:
LotId SomeText AttributeId LotId Kind1Val Kind AttributeId LotId Kind2Val Kind AttributeId LotId Kind3Val Kind
----------- -------- ----------- ----------- -------- -------- ----------- ----------- -------- -------- ----------- ----------- -------- --------
1 Hello 1 1 Foo1 Kind1 2 1 Foo2 Kind2 NULL NULL NULL NULL
2 World 3 2 Bar1 Kind1 4 2 Bar2 Kind2 5 2 Bar3 Kind3
如何从属性表的值中选择数据独立性.
How to choose the data independence away from the value of the attribute table.
错误结果的示例:
LotId SomeText attributeid_1 LotId_1 Value_1 Kind_1 attributeid_2 LotId_2 Value_2 Kind_2 attributeid_3 LotId_3 Value_3 Kind_3
----------- -------- ------------- -------- -------- -------- ------------- -------- -------- -------- ------------- -------- -------- --------
1 Hello 4 1 Foo1 Kind1 NULL NULL NULL NULL NULL NULL NULL NULL
2 World 1 2 Bar2 Kind2 3 2 Bar3 Kind3 2 2 Bar1 Kind8
为什么?
因为Kind_1列中的Kind2文本和Kind_2中的Kind3文本.
Because of Kind2 text in Kind_1 column and Kind3 in Kind_2.
DECLARE @Lot TABLE (
LotId INT PRIMARY KEY IDENTITY,
SomeText VARCHAR(8))
INSERT INTO @Lot
VALUES ('Hello'), ('World')
DECLARE @Attribute TABLE(
AttributeId INT PRIMARY KEY IDENTITY,
LotId INT,
Val VARCHAR(8),
Kind VARCHAR(8))
INSERT INTO @Attribute VALUES
(2, 'Bar2', 'Kind2'),
(2, 'Bar1', 'Kind8'),
(2, 'Bar3', 'Kind3'),
(1, 'Foo1', 'Kind1')
select *
from
(
select LotId,
SomeText,
col+'_'+CAST(rn as varchar(10)) col,
value
from
(
select l.LotId,
l.SomeText,
cast(a.AttributeId as varchar(8)) attributeid,
cast(a.LotId as varchar(8)) a_LotId,
a.Val,
a.Kind,
ROW_NUMBER() over(partition by l.lotid order by a.kind) rn
from @Lot l
left join @Attribute a
on l.LotId = a.LotId
) src
cross apply
(
values ('attributeid', attributeid),('LotId', a_LotId), ('Value', Val), ('Kind', Kind)
) c (col, value)
) d
pivot
(
max(value)
for col in (attributeid_1, LotId_1, Value_1, Kind_1,
attributeid_2, LotId_2, Value_2, Kind_2,
attributeid_3, LotId_3, Value_3, Kind_3)
) piv
正确结果的示例:
LotId SomeText attributeid_Kind1 LotId_Kind1 Value_Kind1 Kind_Kind1 attributeid_Kind2 LotId_Kind2 Value_Kind2 Kind_Kind2 attributeid_Kind3 LotId_Kind3 Value_Kind3 Kind_Kind3
----------- -------- ----------------- ----------- ----------- ---------- ----------------- ----------- ----------- ---------- ----------------- ----------- ----------- ----------
1 WithAll 1 1 Foo1 Kind1 2 1 Foo2 Kind2 3 1 Foo3 Kind3
2 Hello NULL NULL NULL NULL 10 2 Bar2 Kind2 NULL NULL NULL NULL
3 World NULL NULL NULL NULL NULL NULL NULL NULL 12 3 Bar3 Kind3
因为KindX在Kind_x列中，等等.
DECLARE @Lot TABLE (
LotId INT PRIMARY KEY IDENTITY,
SomeText VARCHAR(8))
INSERT INTO @Lot
VALUES ('WithAll'), ('Hello'), ('World')
DECLARE @Attribute TABLE(
AttributeId INT PRIMARY KEY IDENTITY,
LotId INT,
Val VARCHAR(8),
Kind VARCHAR(8))
INSERT INTO @Attribute VALUES
(1, 'Foo1', 'Kind1'),
(1, 'Foo2', 'Kind2'),
(1, 'Foo3', 'Kind3'),
(1, 'Foo4', 'Kind4'),
(1, 'Foo5', 'Kind5'),
(1, 'Foo6', 'Kind6'),
(1, 'Foo7', 'Kind7'),
(1, 'Foo8', 'Kind8'),
(1, 'Foo9', 'Kind9'),
(2, 'Bar2', 'Kind2'),
(2, 'Bar1', 'Kind8'),
(3, 'Bar3', 'Kind3')
DECLARE @AttributesMask TABLE(
Kind VARCHAR(8)
)
INSERT INTO @AttributesMask
VALUES('Kind1'), ('Kind2'), ('Kind3'), ('Kind4'), ('Kind5'), ('Kind6'), ('Kind7'), ('Kind8')
select * from(
select LotId,
SomeText,
--col+'_'+CAST(rn as varchar(10)) col,
col+'_'+[Kind] col,
value
from
(
select l.LotId,
l.SomeText,
cast(a.AttributeId as varchar(8)) attributeid,
cast(a.LotId as varchar(8)) a_LotId,
a.Val,
a.Kind
--, ROW_NUMBER() over(partition by l.[LotId] order by am.[Kind]) rn
FROM @AttributesMask AS am
LEFT join @Attribute a on [am].[Kind] = [a].[Kind]
LEFT JOIN @Lot l ON [a].[LotId] = [l].[LotId]
) src
cross apply
(
values ('attributeid', attributeid),('LotId', a_LotId), ('Value', Val), ('Kind', Kind)
) c (col, value)
) d PIVOT (max(value) for col in (
attributeid_Kind1, LotId_Kind1, Value_Kind1, Kind_Kind1,
attributeid_Kind2, LotId_Kind2, Value_Kind2, Kind_Kind2,
attributeid_Kind3, LotId_Kind3, Value_Kind3, Kind_Kind3)) piv
ORDER BY LotId
为了获得正确的结果，我使用遮罩预先安排了作为PIVOT源的数据.没有面罩怎么办?
To get the correct result I used the mask to pre-arrange the data that are the source of PIVOT. How to do it without the mask?
参考该问题:如何替换功能性(很多)外部应用程序(SELECT * FROM)
推荐答案
除非我在您的解释中遗漏了某些内容，否则您不需要AttributeMask.如果最终的列名只是原始的列名，然后是Kind值，则可以使用:
Unless I am missing something in your explanation, then you do not need the AttributeMask. If the final column names are just going to be the original column names and then the Kind values, then you can use:
select *
from
(
select LotId,
SomeText,
col+'_'+Kind col,
value
from
(
select l.LotId,
l.SomeText,
cast(a.AttributeId as varchar(8)) attributeid,
cast(a.LotId as varchar(8)) a_LotId,
a.Val,
a.Kind
from @Lot l
left join @Attribute a
on l.LotId = a.LotId
) src
cross apply
(
values ('attributeid', attributeid),('LotId', a_LotId), ('Value', Val), ('Kind', Kind)
) c (col, value)
) d
pivot
(
max(value)
for col in (attributeid_Kind1, LotId_Kind1, Value_Kind1, Kind_Kind1,
attributeid_Kind2, LotId_Kind2, Value_Kind2, Kind_Kind2,
attributeid_Kind3, LotId_Kind3, Value_Kind3, Kind_Kind3)
) piv;
请参见带有演示的SQL小提琴.结果如下:
| LOTID | SOMETEXT | ATTRIBUTEID_KIND1 | LOTID_KIND1 | VALUE_KIND1 | KIND_KIND1 | ATTRIBUTEID_KIND2 | LOTID_KIND2 | VALUE_KIND2 | KIND_KIND2 | ATTRIBUTEID_KIND3 | LOTID_KIND3 | VALUE_KIND3 | KIND_KIND3 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | WithAll | 1 | 1 | Foo1 | Kind1 | 2 | 1 | Foo2 | Kind2 | 3 | 1 | Foo3 | Kind3 |
| 2 | Hello | (null) | (null) | (null) | (null) | 10 | 2 | Bar2 | Kind2 | (null) | (null) | (null) | (null) |
| 3 | World | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | 12 | 3 | Bar3 | Kind3 |
这篇关于排序PIVOT源的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
