sql - 每列在单独的行中

Question

如何在单独的行中显示每一列并在最后添加其他字段。

例如我有这个结果:

ID   ArticleName    Brend1     Brend2       Brend3
==   ===========   ========   ========     ========
1    TestArticle    10001      20002        30003

我想实现这个:

ID    ArticleName    BrandNo     BrandName
==    ===========    =======     =========
1     TestArticle     10001       if column name = Brand1 Then Nike
1     TestArticle     20002       if column name = Brand2 Then Adidas
1     TestArticle     30003       if column name = Brand3 Then Mercedes

我可以在单独的行中显示每一列，但是如何在结果 BrandName 的末尾添加额外的列

这是我所做的:

DECLARE @temTable TABLE
(
    Id INT,
    ArticleName VARCHAR(20),
    Brand1 VARCHAR(20),
    Brand2 VARCHAR(20),
    Brand3 VARCHAR(20)
);



INSERT INTO @temTable
(
    Id,
    ArticleName,
    Brand1,
    Brand2,
    Brand3
)
VALUES
(1, 'TestArticle', '10001', '20002', '30003');



SELECT Id,
       ArticleName,
       b.*
FROM @temTable a
    CROSS APPLY
(
    VALUES
        (Brand1),
        (Brand2),
        (Brand3)
) b (Brand)
WHERE b.Brand IS NOT NULL;

Answer 1

你可以使用CROSS APPLY作为

SELECT Id, ArticleName, Br BrandNo, Val BrandName
FROM @TemTable TT
CROSS APPLY(
  VALUES
  (Brand1, 'Nike'),
  (Brand2, 'Adidas'),
  (Brand3, 'Mercedes')
) T(Br, Val)

db-fiddle