Query to display 2 colums data in separate rows

1

Hi

I have 2 tables . What i want if employee is present in 2 shifs in a day then data should be displayed in 2 separate rows.

Employee Master - empcode,empname
Employee Details - empcode,Date,shift1,shif2,shift3,amount

I have written the below Query. Employee can be present in 2 shifts in a day or it can be only one.
Let us say
Empcode = 1 Name = XYZ
Empcode = 2 name = AbC

Empcode = 1 , 20/01/2016 , 1 , 0 , 3 , 4000 . Employee is present in 2 shifts (1 & 3).
Employee = 2 , 19/01/2016 , 1 , 0, 0 , 5000. Employee is present in only 1 shift.

Date should be displayed like this

Empcode = 1

            20/01/2016  1 4000
                             3 4000
                                 
                                8000

Empcode = 2
19/01/2016 1 5000

                                5000

SELECT a.employeecode, max(b.shift1) ,max(b.shift2),max(b.shift3),abs(b.amount)
FROM empmaster AS a
inner join empdetails as b
a.empcode = b.empcode
group by a.empcode

Thanks

2

Something like this perhaps:

with cte(empcode,[date],shift,amount)
  as (select empcode
            ,[date]
            ,shift1
            ,amount
        from empdetails
       where shift1>0
      union all
      select empcode
            ,[date]
            ,shift2
            ,amount
        from empdetails
       where shift2>0
      union all
      select empcode
            ,[date]
            ,shift3
            ,amount
        from empdetails
       where shift3>0
     )
select a.empcode
      ,b.[date]
      ,b.shift
      ,b.amount
  from empmaster as a
       inner join cte as b
               on b.empcode=a.empcode
;