Sql Query To Get The Resultset In Two Columns Only

I have this table: id fName lName Address PostCode ContactNumber ----------------------------------------------------- 1 Tom Daley London EC1 4EQ 075825485665

Solution 1:

You can use the UNPIVOT function to turn the columns into rows:

select id, value
from yourtable
unpivot
(
  value
  for col in ([fName], [lName], [Address], [PostCode], [ContactNumber])
) unpiv

See SQL Fiddle with Demo.

The unpivot will require the datatype on all of the columns to be the same. So you might have to perform a cast/convert on any columns with different datatypes similar to this:

select id, value
from
(
  select id, [fName], [lName], [Address], [PostCode],
    cast([ContactNumber] as varchar(15)) [ContactNumber]from yourtable
) src
unpivot
(
  value
  for col in ([fName], [lName], [Address], [PostCode], [ContactNumber])
) unpiv;

See SQL Fiddle with Demo.

Starting in SQL Server 2008, this can also be written using a CROSS APPLY with a VALUES:

select t.id,
  c.value
from yourtable t
cross apply
(
  values(fName), 
    (lName), 
    (Address), 
    (PostCode), 
    (cast(ContactNumber as varchar(15)))
) c (value)

See SQL Fiddle with Demo

Solution 2:

How about something like this:

SELECT
 id, fName as label
FROMtableUNIONALLSELECT
 id, lName
FROMtableUNIONALLSELECT
 id, Address
FROMtable

...etc