Each Column in Separate Row

Question

How can I display each column in separate row and at the end add additional field.

For example I have this result:

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

I want to achieve this:

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

I can show each column in separate row, but how can I add additional column to the end of the result BrandName

Here is what I've done:

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;

user_6426692 · Answer

You could use CROSS APPLY as

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

db-fiddle

user_2029983 · Answer

I assume the brand is stored in another table, so you just need to add another column in your VALUES operator, and then join to the Brand Table:

SELECT Id,
       ArticleName,
       V.Brand
FROM @temTable a
     CROSS APPLY (VALUES (1,Brand1),
                         (2,Brand2),
                         (3,Brand3)) V (BrandID,Brand)
     JOIN dbo.Brand B ON V.BrandID = B.BrandID
WHERE V.Brand IS NOT NULL;

user_2041092 · Answer

You can use UNPIVOT to achieve this. You can use either a case statement or another table variable to switch column names with brand names, I would prefer a table variable with a join it would make adding new column a bit easier.

DECLARE @d TABLE (ColNames VARCHAR(128) , BrandName VARCHAR(100))

INSERT INTO @d VALUES ('Brand1', 'Nike'),('Brand2', 'Adidas'),('Brand3', 'Mercedes')


SELECT up.Id
     , up.ArticleName
     , up.BrandNo
     , d.BrandName
FROM @temTable
UNPIVOT (BrandNo FOR ColNames IN (Brand1,Brand2,Brand3)) up
INNER JOIN @d d ON d.ColNames = up.ColNames

Each Column in Separate Row

3 Answers