June 11, 2015 at 11:42 pm
Hello to all SQL Experts ,
I've been working on this query for some time now, I've searched this site and tried some examples but could not get the results that I'm after.
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
If someone kindly help I will be grateful.
Thank you,
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
)
,TEST_TEST AS
( SELECT
SD.ColumnA,
SD.ColumnB,
SD.ColumnC,
SD.ColumnD,
'SampleTitle'+
cast(row_number() over(partition by SD.columna, SD.columnb
order by SD.columnd) as varchar(10)) seq
from SampleData SD
)
select columnA, columnB,columnc
SampleTitle1, SampleTitle2, SampleTitle3, SampleTitle4
from
(
SELECT
[ColumnA],
[ColumnB],
[ColumnC],
[ColumnD],
'SampleTitle'+
cast(row_number() over(partition by columna, columnb
order by columnd) as varchar(10)) seq
from TEST_TEST TT
) d
pivot
(
max(columnd)
for seq in (SampleTitle1, SampleTitle2, SampleTitle3, SampleTitle4)
) piv;
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
June 12, 2015 at 12:47 am
Quick solution that works with the sample data provided
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
)
,UNPIVOTED_SET AS
(
SELECT
SD.ColumnA
,SD.ColumnB
,ROW_NUMBER() OVER
(
PARTITION BY SD.ColumnA
,SD.ColumnB
ORDER BY TIME_X.TIME_VAL ASC
) AS TIME_RID
,TIME_X.TIME_VAL
FROM SampleData SD
CROSS APPLY
(
SELECT ColumnC AS TIME_VAL UNION ALL
SELECT ColumnD AS TIME_VAL
) AS TIME_X
)
SELECT
US.ColumnA AS PERSON
,US.ColumnB AS STARTDATE
,MAX(CASE WHEN US.TIME_RID = 1 THEN US.TIME_VAL END) AS STARTIME1
,MAX(CASE WHEN US.TIME_RID = 2 THEN US.TIME_VAL END) AS ENDTIME1
,MAX(CASE WHEN US.TIME_RID = 3 THEN US.TIME_VAL END) AS STARTTIME2
,MAX(CASE WHEN US.TIME_RID = 4 THEN US.TIME_VAL END) AS ENDTIME2
FROM UNPIVOTED_SET US
GROUP BY US.ColumnA
,US.ColumnB;
Results
PERSON STARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
----------- ---------- --------- -------- ---------- --------
5060 04/30/2015 05:30 08:30 13:30 15:30
5060 05/02/2015 05:30 08:30 13:30 15:30
June 12, 2015 at 3:17 pm
Hi Eirikur Eiriksson,
You are the man 🙂
I really appreciate your help for the second time 🙂
Can you please guide me to an article or a site where I can further understand/enhance my knowledge of the logic/codes you used to solve my issue?
Best Regards and have an awesome weekend !!
June 12, 2015 at 5:26 pm
DiabloZA (6/12/2015)
Hi Eirikur Eiriksson,You are the man 🙂
I really appreciate your help for the second time 🙂
Can you please guide me to an article or a site where I can further understand/enhance my knowledge of the logic/codes you used to solve my issue?
Best Regards and have an awesome weekend !!
It's called a CROSSTAB. The following two articles explain it for both fixed and dynamic applications. When using character based columns, you use MAX like Eirikur did instead of SUM.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply