How to write dynamic query columns into rows in SQL Server?

Question

I have a question about in SQL Server: how to combine 4 columns into 1 column in a query.

Table: emp

empid | addr | sal | doj
------+------+-----+------------
  1   | hyd  | 10  | 10-01-1990
  2   | del  | 20  | 12-03-1999
  1   | pune | 50  | 12-03-2017

Based on above data I want output like below

empid | empvalues
------+---------------
1     | hyd
1     | 10
1     | 10-01-1990
2     | del
2     | 20
2     | 12-03-1999
1     | pune
1     | 50
1     | 12-03-2017

I tried with a query like this:

select 
    empid, cast (addr as varchar(100)) as empvalues 
from emp

union all

select empid, cast (sal as varchar(100)) as empvalues 
from emp

union all

select empid, cast (doj as varchar(100)) as empvalues 
from emp

This query is returning the correct result, but it takes a lot of time due to calling same table 3 times.

Can you please tell me how to write any alternative query to achieve this task in SQL Server?

Answer 1

You could try using UNPIVOT, like this...

SELECT empid, empvalues
FROM (
    select empid, addr, convert(varchar(100), sal) as sal,  convert(varchar(100), doj, 103) as doj
    from emp
) pv
UNPIVOT
(
    empvalues
    FOR ev in (addr, sal, doj)
) AS ev

produces this output...

Resources for your reference...

https://codingsight.com/understanding-pivot-unpivot-and-reverse-pivot-statements/ https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15