SQL Columns to List

Question

I have the following table contents:-

And I need to produce the following output:-

P1 C1
P1 C3
P2 C1
P2 C4
P3 C2
P3 C3
P3 C4

How can I create that list from my table?

Thanks

P

Answer 1

You have to use UnPivot to get the desired result

    DECLARE @MyTable TABLE
(Attribute VARCHAR(10) , C1 VARCHAR(10), C2 VARCHAR(10), C3 VARCHAR(10), C4 VARCHAR(10))

INSERT INTO @MyTable VALUES
('P1','X', NULL,'X',NULL), ('P2','X',NULL,NULL,'X'),('P3',NULL,'X','X','X')

SELECT * FROM @MyTable

SELECT uPivot.Attribute, uPivot.Quatr, uPivot.IsMarked
FROM @MyTable Tab1
UNPIVOT
        (
          IsMarked
          for Quatr in (C1, C2, C3, C4)
        ) uPivot;

SQL FIDDLER EXAMPLE