How to write a query which will result in the following format?

Question

I think for this kind of result I have to use union but the problem is that I dont know the number of columns.That's why I will not be able to use Union.

Answer 1

It looks like you are trying to unpivot the data instead of applying a pivot. The UNPIVOT function will convert your multiple columns into rows.

If you know how many columns you will have, then you can hard-code the solution:

select emp_id, col, value
from
(
  select emp_id, name, cast(age as varchar(20)) age, d.title
  from empTable e
  inner join empDesignationTable d
    on e.designationId = d.id
) d
unpivot
(
  value
  for col in (name, age, title)
) u;

See SQL Fiddle with Demo.

But if you are going have unknown columns, then you will need to use dynamic SQL:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.column_name)
         from information_schema.columns as C
         where C.table_name in ('empTable', 'empDesignationTable') and
               C.column_name not like '%id%'
         for xml path('')), 1, 1, '')

set @query 
  = 'select emp_id, col, value
     from
     (
       select emp_id, name, cast(age as varchar(20)) age, d.title
       from empTable e
       inner join empDesignationTable d
         on e.designationId = d.id
     ) d
     unpivot
     (
        value
        for col in ('+ @colsunpivot +')
     ) u'

exec(@query)

See SQL Fiddle with Demo. Both give the result:

| EMP_ID |   COL |             VALUE |
--------------------------------------
|      1 |  name |              John |
|      1 |   age |                25 |
|      1 | title | Software Engineer |
|      2 |  name |             Smith |
|      2 |   age |                31 |
|      2 | title |       UI Designer |