Show column name along with column value in SQL Server

Question

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.

Answer 1

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.