How to transform table with several columns into a keyword table

Question

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?

Does this answer your question? [SQL Server : Columns to Rows](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — YHF, Aug 11 '22 at 05:02

score 1 · Accepted Answer · answered Aug 11 '22 at 04:20

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)

How to transform table with several columns into a keyword table

1 Answers1