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

Question

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.

What does your schema look like? Which RDBMS? It looks like you're trying to `UNPIVOT` here. Is that correct? — lc., Apr 04 '13 at 06:55
This picture is the desired output, could you share your input table schema and sample data. — ljh, Apr 04 '13 at 06:59
I can achieve this result using this query.SELECT emp_id, 'Name' AS [col_name], Name AS [col_value] FROM empTable UNION SELECT emp_id, 'Age', Age FROM empTable UNION SELECT t1.emp_id, 'Designation', ed.designation FROM ( SELECT e.emp_id, des.id, des.title FROM empTable e FULL OUTER JOIN designation des ON des.Title IS NOT NULL ) AS [t1] LEFT JOIN empDesignationTable ed ON t1.emp_id = ed.emp_id AND t1.id = ed.des_id GROUP BY t1.emp_id, t1.id, t1.title — Vimal Patel, Apr 04 '13 at 07:07

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

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

edited May 23 '17 at 10:25

Community

1
1

answered Apr 04 '13 at 06:55

WernerW

792
11
27

I have created a dynamic pivot query but that will not going to help me. – Vimal Patel Apr 04 '13 at 06:59

score 0 · Answer 2 · answered Apr 04 '13 at 07:21

0

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

answered Apr 04 '13 at 07:21

ljh

2,546
1
14
20

but you have to know the column names before writing this query. – Vimal Patel Apr 04 '13 at 07:46
1

In what kind of scenario, you can't know your table schema, can't know your table name. If you can't know the table schema, for example no permission to view table DDL, then you have no way to get it anywhere like from category views. If you have already know your table schema, why you need dynamic unpivot, it is much more complex, everything is cost driven. – ljh Apr 04 '13 at 15:38

score 0 · Accepted Answer · answered Apr 04 '13 at 10:16

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 |

@BF, this is not fully dynamic yet. Like if table emptable has another column [MgrID int], then that column will not be pivot. And as we know, unpivot columns has to be same data type and length, so, dynamic check column data type definition, dynmaic cast to same varchar length is needed. — ljh, Apr 04 '13 at 15:34

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

3 Answers3