CODEWITHSUNDEEP
sqlsql-serversql-server-2008unpivot
HEEN
HEEN

Reputation: 4721

Transform column into rows in SQL Server table

I have a query in which I want some columns to be appear as rows.

The query is 

Select * 
From Emp_mon_day 
Where emp_mkey IN (select emp_card_no 
                   from emp_mst 
                   where comp_mkey in (7, 110)) 
  and Year = 2016 and month = 2 
  and Emp_mkey = 2492

with this output being returned:

Image

Now, I need to show columns Day1, Day2, Day3 as rows in the output with the above query.

How to achieve that?

Upvotes: 1

Views: 173

Answers (2)

Gautam G
Gautam G

Reputation: 494

CROSS APPLY can be proven helpful here.

CREATE TABLE [dbo].[emp]
(
    [comp_mkey]    [INT] NULL,
    [mont]         [INT] NULL,
    [year]         [INT] NULL,
    [day1]         [VARCHAR](10) NULL,
    [day2]         [VARCHAR](10) NULL
)

INSERT INTO emp VALUES (2, 2, 2016, 'AB', 'AC')

Use following Select statement

SELECT emp.comp_mkey
   , emp.mont
   , emp.year
   , emp_ext.[Day]
   , emp_ext.Value 
FROM emp
CROSS APPLY 
(
    VALUES('Day1', emp.day1), ('Day2', emp.day2)
)emp_ext([Day], Value)

result

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use case an UNPIVOT query like below

Select 
comp_mkey, 
fmodule_id,
fdepartment_id,
branch_mkey,
entry_department,
dept_mkey,
mkey,
emp_mkey,
entry_date,
month,
year,
day,
data
from
(select * from Emp_mon_day where emp_mkey IN 
(select emp_card_no from emp_mst where comp_mkey in
(7,110)) and Year = 2016 and month = 2 
and Emp_mkey = 2492) s
unpivot
(
data for day in ([Day1],[Day2]) -- dynamic query can generate all days data
)up

Below is sample test script and output 

create table t(comp_mkey int,mont int,yea int,day1 varchar(10),day2 varchar(10))
insert into t values (2,2,2016,'AB','AC')

Select 
comp_mkey, 
mont,
yea,
day,
data
from
(select * from t) s
unpivot
(
data for day in ([Day1],[Day2]) -- dynamic query can generate all days data
)up
drop table t

Output

enter image description here

If you need all day's data you can either type out all expected columns in this statement

data for day in ([Day1],[Day2], [Day3],[Day4])

Better way would be to convert this into a dynamic query and apply logic for number of days expected in a month

Upvotes: 2

Related Questions