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.
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.