sql - 根据另一个表中的列值转置一个表中的行
问题描述
我问了一个类似的问题 [here]:将具有相同 ID 的行组转置到另一个表中
,但我不想为每个属性名称写出每一行。我希望能够从 TABLE1 的一列中获取行(比如有 50 个唯一行)并将它们有效地转置到 TABLE2 中。
这是一列中的 ATTR_NME(属性名称)表的一个小示例:
ID ATTR_NME ATTR_VAL
1000 UPC 00015582981001
1000 Price 15.99
1000 Brand Oreo
1005 UPC 00038762291010
1005 Price 12.50
1005 Brand Sargento
1010 UPC 00198872499000
1010 Price 4.99
1010 Brand Olay
所以 ID、UPC、Price 和 Brand 应该是它们自己的列,ATTR_VAL 作为数据行。这是这个问题和我上一个问题之间的区别:假设我有一个包含 50 个不同 ATTR_NME 的表,我想使用具有所有这些属性名称的数据列来创建新的转置表,而无需为以下 50 次编写不同的属性:
MAX(CASE WHEN ATTR_NME = 'UPC' THEN ATTR_VAL END) AS UPC
我试图为 ATTR_NME 列中的行声明一个变量,如下所示:
DECLARE @itm varchar(100);
SET @itm = (
SELECT ATTR_NME
FROM TABLE1
);
SELECT ID,
MAX(CASE WHEN ATTR_NME = @itm THEN ATTR_VAL END) AS [@itm]
FROM TABLE1
GROUP BY ID;
但这并没有奏效,原因有很多。一个是它希望我在我设置 @itm 的 ATTR_NME 周围包装一个聚合函数(但后来我只有一个属性名称)。它也没有像我想要的那样标记/命名列。甚至可以做我想做的事吗?如果是这样,怎么做?TIA。
解决方案
你在谈论Pivot
吗?
如果您需要pivot
此表,则无法避免将ATTR_NME
50 次放入以下查询中(请参阅评论)
WITH A
AS
(
SELECT '1000' as ID, 'UPC ' as ATTR_NME, '00015582981001' as ATTR_VAL
UNION ALL SELECT '1000' as ID, 'Price' as ATTR_NME, '15.99 ' as ATTR_VAL
UNION ALL SELECT '1000' as ID, 'Brand' as ATTR_NME, 'Oreo ' as ATTR_VAL
UNION ALL SELECT '1005' as ID, 'UPC ' as ATTR_NME, '00038762291010' as ATTR_VAL
UNION ALL SELECT '1005' as ID, 'Price' as ATTR_NME, '12.50 ' as ATTR_VAL
UNION ALL SELECT '1005' as ID, 'Brand' as ATTR_NME, 'Sargento ' as ATTR_VAL
UNION ALL SELECT '1010' as ID, 'UPC ' as ATTR_NME, '00198872499000' as ATTR_VAL
UNION ALL SELECT '1010' as ID, 'Price' as ATTR_NME, '4.99 ' as ATTR_VAL
UNION ALL SELECT '1010' as ID, 'Brand' as ATTR_NME, 'Olay ' as ATTR_VAL
)
SELECT *
FROM A
PIVOT
(MAX(ATTR_VAL) FOR ATTR_NME in ([Price],[Brand],[UPC]) ) -- here you need to put all the 50 ATTR_NAME
as PIVOTTABLE