How to display different Table output from an existing table in SQL?

Question

I have this existing table,

and i want to select and display an output like this:

Is it possible to do this in SQL?

If it's coming from another table then you can join two table to have your desired output. — Badhon Jain, Feb 16 '17 at 07:02
there is no extra table, I just want to kinda re arrange it, just for viewing — jessemiel, Feb 16 '17 at 07:07
Now I got it, you want to transform your rows into columns. You can use pivot function. Refer to this: http://stackoverflow.com/questions/3120835/how-to-pivot-rows-into-columns-custom-pivoting — Badhon Jain, Feb 16 '17 at 07:11
first, what you have tried, that paste here and where are you fail — Ajay2707, Feb 16 '17 at 07:25

score 1 · Answer 1 · edited May 23 '17 at 12:09

Use CASE statements:

SELECT EmpNo, DTRDATE, 
(CASE WHEN TRANS_TYPE = 'Shift' THEN DT_START ELSE NULL END) TimeIN,
(CASE WHEN TRANS_TYPE = 'Shift' THEN DT_END ELSE NULL END) TimeOUT,
(CASE WHEN TRANS_TYPE = 'AMBreak' THEN DT_START ELSE NULL END) AMIN,
(CASE WHEN TRANS_TYPE = 'AMBreak' THEN DT_START ELSE NULL END) AMOUT,
etc...

FROM YourTable

Read this question for more clearance: SQL Case Statement Syntax?

score 1 · Answer 2 · answered Feb 16 '17 at 08:15

I wish it help you

SELECT t1.EmpNo, t1.DTRDATE,
(CASE WHEN t1.DT_START is not null THEN t1.DT_START ELSE Null END) TimeIN,
(CASE WHEN t1.DT_START is not null THEN t1.DT_END ELSE Null END) TimeOUT,
(CASE WHEN t2.DT_START is not null THEN t2.DT_START ELSE Null END) AMIN,
(CASE WHEN t2.DT_START is not null THEN t2.DT_END ELSE Null END) AMOUT,
(CASE WHEN t3.DT_START is not null THEN t3.DT_START ELSE Null END) LUNCHIN,
(CASE WHEN t3.DT_START is not null THEN t3.DT_END ELSE Null END)  LUNCHOUT,
(CASE WHEN t4.DT_START is not null THEN t4.DT_START ELSE Null END) PMIN,
(CASE WHEN t4.DT_START is not null THEN t4.DT_END ELSE Null END) PMOUT
FROM 
(select * from  Table_1 where TRANS_TYPE = 'SHIFT') AS t1 left join
(select * from  Table_1 where TRANS_TYPE = 'AMBREAK') AS t2 on t1.EMPNO = t2.EMPNO and t1.DTRDATE = t2.DTRDATE left join
(select * from  Table_1 where TRANS_TYPE = 'LUNCH') AS t3 on t1.EMPNO = t3.EMPNO and t1.DTRDATE = t3.DTRDATE left join
(select * from  Table_1 where TRANS_TYPE = 'PMBREAK') AS t4 on t1.EMPNO = t4.EMPNO and t1.DTRDATE = t4.DTRDATE

score 0 · Answer 3 · answered Feb 16 '17 at 07:30

Self join can also be useful. Don't have the SQL Server right now. Query might look like

select 
EMPNO, 
DTRDate,
T1.DT_START as ’TIME IN’,
T1.DT_END as ’TIME OUT’,
CASE T2.DT_END
    WHEN T2.DT_END IS NOT NULL 
    THEN T2.DT_END
    ELSE ‘-’
END AS  ‘AM OUT’,
CASE T2.DT_START
    WHEN T2.DT_START IS NOT NULL 
    THEN T2.DT_START
    ELSE ‘-’
END AS  ‘AM IN’,

CASE T3.DT_END
    WHEN T3.DT_END IS NOT NULL 
    THEN T3.DT_END
    ELSE ‘-’
END AS  ‘LUNCH OUT’,
CASE T3.DT_START
    WHEN T3.DT_START IS NOT NULL 
    THEN T3.DT_START
    ELSE ‘-’
END AS  ‘LUNCH IN’,

CASE T4.DT_END
    WHEN T4.DT_END IS NOT NULL 
    THEN T4.DT_END
    ELSE ‘-’
END AS  ‘PM OUT’,
CASE T4.DT_START
    WHEN T4.DT_START IS NOT NULL 
    THEN T4.DT_START
    ELSE ‘-’
END AS  ‘PM IN’,
FROM TABLENAME AS T1
LEFT outer JOIN TABLENAME AS T2 ON T1.EMPNO == T2.EMPNO AND T1.TRANS_TYPE = ’SHIFT’ AND T2.TRANS_TYPE= ‘AMBREAK’ AND T1.TRANS_TYPE == T2.TRANS_TYPE AND T1.DTRDATE == T2.DTRDATE
LEFT OUTER JOIN TABLENAME AS T3 ON T2.EMPNO == T3.EMPNO AND T2.DTRDATE == T3.DTRDATE AND T3.TRANS_TYPE = ’LUNCH’ AND T2.TRANS_TYPE == T3.TRANS_TYPE
LEFT OUTER JOIN TABLENAME AS T4 ON T3.EMPNO == T4.EMPNO AND T4.DTRDATE == T3.DTRDATE AND T4.TRANS_TYPE = ’PMBREAK AND T3.TRANS_TYPE == T4.TRANS_TYPE
where T1.TRANS_TYPE = ’SHIFT’

score 0 · Accepted Answer · answered Feb 16 '17 at 07:47

I asked my friend (Master Mico) about this and gave me this answer, and it works just right. here's the answer with slightly different column name.

SELECT
PVTIN.EMPNO, PVTIN.DTRDATE, PVTIN.TIMEIN, PVTOUT.[TIMEOUT], PVTIN.[AM OUT], PVTOUT.[AM IN], PVTIN.[LUNCH OUT], PVTOUT.[LUNCH IN], PVTIN.[PM OUT], PVTOUT.[PM IN]

FROM
(SELECT EMPNO, DTRDATE, [SHIFT] AS TIMEIN, AMBREAK AS [AM OUT], [LUNCH] AS [LUNCH OUT], [PMBREAK] AS [PM OUT] FROM
(select * from
(Select EMPNO, CONVERT(DATE, DTRDATE) AS DTRDATE, TRANS_TYPE, DT_START from dtr_emp_trans) TIN

PIVOT
(MAX(DT_START) FOR TRANS_TYPE IN ([SHIFT],[AMBREAK],[LUNCH],[PMBREAK])) PVT) PVTIN) PVTIN

LEFT JOIN
(SELECT EMPNO, DTRDATE, [SHIFT] AS TIMEOUT, AMBREAK AS [AM IN], [LUNCH] AS [LUNCH IN], [PMBREAK] AS [PM IN] FROM
(select * from
(Select EMPNO, CONVERT(DATE, DTRDATE) AS DTRDATE, TRANS_TYPE, DT_END from dtr_emp_trans) TIN

PIVOT
(MAX(DT_END) FOR TRANS_TYPE IN ([SHIFT],[AMBREAK],[LUNCH],[PMBREAK])) PVT) PVTOUT) PVTOUT
ON PVTIN.EMPNO = PVTOUT.EMPNO AND PVTIN.DTRDATE = PVTOUT.DTRDATE

yeah. I that's it.

score 0 · Answer 5 · answered Feb 16 '17 at 07:59

I'm late to the party, but took me longer to find a solution..

create table #temp(
empno int,
dtrdate date,
trans_type nvarchar(18),
dt_start time,
dt_end time
)

insert into #temp(empno,dtrdate,trans_type,dt_start,dt_end)
values 
(28, '01/02/2017','shift','10:53:27','22:05:47'),
(28, '01/02/2017','ambreak','12:00:27','12:14:47'),
(28, '01/02/2017','lunch','16:00:27','16:05:47'),
(28, '01/03/2017','shift','12:47:27','22:47:47'),
(28, '01/03/2017','ambreak','14:02:27','14:11:47'),
(28, '01/03/2017','lunch','15:01:27','15:52:47'),
(28, '01/03/2017','pmbreak','20:32:27','20:40:47')


Select empno, dtrdate,
max(case trans_type when 'shift' then dt_start else null end) as [time in],
max(case trans_type when 'shift' then dt_end else null end) as [time out]
......
......
from #temp
group by empno, dtrdate

How to display different Table output from an existing table in SQL?

5 Answers5