sql—将多个列与相应的值对应起来

dldeef67 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(304)

尝试将多个列放入行中。预期结果是

这是我试过的样本数据。我对unpivot也持开放态度，如果总体上更快的话。完整数据有15个attributeid、attributedata列。

DROP TABLE Attribute;

CREATE TABLE Attribute 
(
        Producttitle varchar(200),  
        AttributeID_1 varchar(50),  
        AttributeData_1 varchar(50), 
        AttributeID_2   varchar(50),
        AttributeData_2 varchar(50),
        AttributeID_3 varchar(50),  
        AttributeData_3 varchar(50)
);

INSERT INTO Attribute 
VALUES ('title1', '3145', 'Specific', '30', 'Yes', '40', 'Pink')

INSERT INTO Attribute 
VALUES ('title2', '17', 'Stainless', '19', 'smoke', '19', 'Something');

SELECT
    Producttitle, 
    [AttributeID],
    [AttributeData] 
FROM
    Attribute
CROSS APPLY
    (SELECT 'Indicator1', [AttributeID_1] UNION ALL
     SELECT 'Indicator2', [AttributeID_2] UNION ALL
     SELECT 'Indicator3', [AttributeID_3]) c (indicatorname, [AttributeID])
CROSS APPLY
    (SELECT 'Indicator1', [AttributeData_1] UNION ALL
     SELECT 'Indicator2', [AttributeData_2] UNION ALL
     SELECT 'Indicator3', [AttributeData_3]) d (indicatorname, [AttributeData]);

sql sql-server unpivot cross-apply

来源：https://stackoverflow.com/questions/63320153/multiple-columns-to-row-with-respective-values

2条答案

按热度按时间

ee7vknir1#

你可以用 cross apply 取消激活数据集。用它更简单 values() :

select a.title, x.*
from attribute a
cross apply (values
    (a.attributeId_1, a.attributeData_1),
    (a.attributeId_2, a.attributeData_2),
    (a.attributeId_3, a.attributeData_3)
) as x(attributeId, attributeData)

请注意，这是因为这两组列具有一致的数据类型，否则需要进行额外的转换。

赞(0）回复(0）举报 2021-07-24

axzmvihb2#

gmb的解决方案确实很酷，但基本工会也会奏效：

SELECT Producttitle, AttributeID_1 AttributeID, AttributeData_1 AttributeData
from attribute
union
SELECT Producttitle, AttributeID_2 AttributeID, AttributeData_2 AttributeData
from attribute
union
SELECT Producttitle, AttributeID_3 AttributeID, AttributeData_3 AttributeData
from attribute

赞(0）回复(0）举报 2021-07-24

我来回答

sql—将多个列与相应的值对应起来

2条答案

相关问题

热门标签

最新问答