我有两个表。第一个表包含产品及其属性。第二个表包含属性的元数据。示例数据如下:
1.
+-----------------------------------+------------+--------+------------+
| Product | Level1 | Level2 |SKU |
+-----------------------------------+------------+--------+------------+
| Hard Drive | 2.5 | 6 |342-0429 |
+-----------------------------------+------------+--------+------------+
| Hard Drive | 3.5 | 6 |342-0445 |
+-----------------------------------+------------+--------+------------+
阿拉伯数字。
+-----------------------------------+------------+--------+
| Product | Level1 | Level2 |
+-----------------------------------+------------+--------+
| Hard Drive | FormFactor | Speed |
+-----------------------------------+------------+--------+
我希望输出采用以下格式:
+-----------------------------------+------------+--------+------------+
| Product | FormFactor | Speed |SKU |
+-----------------------------------+------------+--------+------------+
| Hard Drive | 2.5 | 6 |342-0429 |
+-----------------------------------+------------+--------+------------+
| Hard Drive | 3.5 | 6 |342-0445 |
+-----------------------------------+------------+--------+------------+
需要这方面的帮助。我已经在 SQL 中检查了透视和取消透视,但是大多数解决方案都指向使用 1 个表执行此操作。我还没有遇到使用两个表作为上述情况的解决方案。我想知道是否有任何需要根据需要查询输出,而不是创建一个更改所有列名或使用 alter 更改现有表的新表。这只是一个包含一种产品的示例表。但是,实际表包含大约 20 种产品,具有数千个 SKU 及其属性。
示例数据
DECLARE @Table1 AS TABLE
(
Product varchar(100),
Level1 DEcimal(2,1),
Level2 INT,SKU varchar(100)
)
INSERT INTO @Table1
SELECT 'Hard Drive',2.5,6,'342-0429' UNION ALL
SELECT 'Hard Drive', 3.5,6,'342-0445'
DECLARE @Table2 AS TABLE
(
Product varchar(100),
Level1 varchar(100),
Level2 varchar(100)
)
INSERT INTO @Table2
SELECT 'Hard Drive','FormFactor','Speed'
试试下面的脚本
SELECT [Product],[FormFactor],[Speed],SKU
FROM
(
SELECT A.Product,
A.Level1 ,
A.Level2 ,
B.Level1 As BFormFactor,
B.Level2 AS BSpeed,
A.SKU
FROM @Table1 A
JOIN @Table2 B
ON A.Product=b.Product
)
AS SRc
PIVOT
(
MAX(Level1) FOR BFormFactor IN([FormFactor])
)Pvt
PIVOT
(
MAX(Level2) FOR BSpeed IN([Speed])
)Pvt2
结果,演示:http://rextester.com/OOLVX55531
Product FormFactor Speed SKU
------------------------------------------
Hard Drive 2.5 6 342-0429
Hard Drive 3.5 6 342-0445