How to transform table with several columns into a keyword table

byMR

August 11, 2022

I have a table with columns like CustID, FirstName, LastName, PhoneNumber, Membershiplevel, etc.

I want to produce a table with these columns: CustID, Keyword, Value.

The column names would be the keywords, and there would be a row for every keyword.

So this:

CustID, FirstName, LastName, PhoneNumber,  MembershipLevel
1234    Joe        Smith     555-555-5555, Select

Would become

CustID, Keyword    , Value
1234  , FirstName  , Joe
1234  , LastName   , Smith
1234  , PhoneNumber, 555-555-5555
1234  , MembershipLevel, Select

I know I could painstakingly accomplish this using dynamic SQL, but is there a straightforward, "SQL" way to do this without resorting to procedural T-SQL?

>Solution :

use CROSS APPLY operator with VALUES constructor

select a.*
from   tbl
       cross apply
       (
           values
           (CustID, 'FirstName', FirstName),
           (CustID, 'LastName', FirstName),
           (CustID, 'PhoneNumber', PhoneNumber),
           (CustID, 'MembershipLevel',MembershipLevel)
       ) a (CustID, KeyWord, Value)