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

enter image description here

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.


ANSWERS:


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 |

You could create a dynamic Pivot SQL query. Usually Dynamic queries are bad but sometimes they cant be helped. You should consider changing your database structure if you dont know how many columns are to be shown, suddenly you post someones personal details unintentionally ...

A similar topic with more in depth details can be found here: SQL Pivot Query with Dynamic Columns


SQL FIDDLE DEMO
This is for how many columns you know you need to unpivot.


select ID, ColumnName, ColumnValue
from (
select ID, Name, cast(Age as varchar(10)) as Age, Designation from Employee 
)P
unpivot
   (
      columnValue for ColumnName in ([Name], [Age], [Designation]) 
    ) as UP


 MORE:


 ? How do I PIVOT in SQL Server with dynamic columns, values, and datatypes?
 ? Multi Column Dynamic Pivot Table
 ? Dynamic PIVOT with Numbered Column Names
 ? SQL Server dynamic PIVOT query - fields order
 ? T-SQL dynamic pivot
 ? T-SQL dynamic pivot
 ? T-SQL dynamic pivot
 ? Converting Row to Column dynamically in SQL
 ? I'm currently looking for a dynamic way to convert rows to columns in a specific way in SQL server
 ? Microsoft SQL Server: Adding New Column To Row Instead Of Repeated Values