sqlsql-serverpivotsql-server-2019

Show column name along with column value in SQL Server


I have a following table:

declare @tab1 table(
    Id int identity,
    address1 varchar(100),
    city varchar(50),
    name varchar(50),
    phone varchar(10)
)

insert into @tab1 (address1, city, name, phone) values
    ('C-10', 'UX', 'A', '9898989898'),
    ('A-19', 'DX', 'B', '8888888888')

select * from @tab1

I want the output as column name along with column value based on id. Like below.

This script is my desired output

declare @tab2 table(
    item varchar(100),
    value varchar(200)
)

insert into @tab2 (item, value) values
    ('Address1', 'C-10'),
    ('city', 'UX'),
    ('name', 'A'),
    ('phone', '9898989898')

select * from @tab2

I'm unable to write this query that shows column name along with its column value.


Solution

  • You can do it with CROSS APPLY and row-value-constructor:

    SELECT t.id, ca.*
    FROM @tab1 AS t
    CROSS APPLY (VALUES
        ('address1', t.address1),
        ('city',     t.city),
        ('name',     t.name),
        ('phone',    t.phone)
    ) AS ca(col_name, col_val)
    

    The query assumes that the data-type of all columns is compatible.