'SQL Server Pivot with Multiple Rows Output

I have the following data in a Table.

And i need output in below format.

I tried pivoting but seems like not able to resolve it. Can someone please guide me here?

Thanks in advance.

Edit: Data in Text Format.

EmployeeID	ShiftCode	AttendanceDate	inDateTime	outDateTime	OverTimeHours
26	ShiftCC1	01-03-2022	01-03-2022 09:10	01-03-2022 18:10	1
26	ShiftCC1	02-03-2022	02-03-2022 09:15	02-03-2022 18:15	2
26	ShiftCC1	03-03-2022	03-03-2022 09:05	03-03-2022 18:05	2
26	ShiftCC1	04-03-2022	04-03-2022 09:10	04-03-2022 18:10	1
26	ShiftCC1	05-03-2022	05-03-2022 09:13	05-03-2022 18:13	2
26	ShiftCC1	06-03-2022	06-03-2022 09:14	06-03-2022 18:14	3
26	ShiftCC1	07-03-2022	07-03-2022 09:16	07-03-2022 18:16	2
26	ShiftCC1	08-03-2022	08-03-2022 09:30	08-03-2022 18:30	1
26	ShiftCC1	09-03-2022	09-03-2022 09:20	09-03-2022 18:20	2
26	ShiftCC1	10-03-2022	10-03-2022 09:25	10-03-2022 18:25	3

output in Text Format:

EmployeeID	ShiftCode	DataType	01-03-2022	02-03-2022	03-03-2022	04-03-2022	05-03-2022	06-03-2022	07-03-2022	08-03-2022	09-03-2022	10-03-2022
26	ShiftCC1	InDateTime	01-03-2022 09:10	02-03-2022 09:15	03-03-2022 09:05	04-03-2022 09:10	05-03-2022 09:13	06-03-2022 09:14	07-03-2022 09:16	08-03-2022 09:30	09-03-2022 09:20	10-03-2022 09:25
26	ShiftCC1	OutDateTime	01-03-2022 18:10	02-03-2022 18:15	03-03-2022 18:05	04-03-2022 18:10	05-03-2022 18:13	06-03-2022 18:14	07-03-2022 18:16	08-03-2022 18:30	09-03-2022 18:20	10-03-2022 18:25
26	ShiftCC1	OverTimeHours	1	2	2	1	2	3	2	1	2	3

sql-server pivot

Solution 1:^[1]

First of all, you can' t have the exactly output you want with your dataset. Either you need to give up your overtime column because after pivotting those columns automatically convert to date type or you could change datatype of your columns(I used view for that).

Also, I assumed your "AttendanceDate" is dynamic, so I used a dynamic solution.

I used @Taryn' s solution here:

Option 1:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.att_date) 
            FROM test1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT idx, name, ' + @cols + ' from 
            (
                select idx,
                       name,
                    att_date,
                    att_entry
                from test1
           ) x
            pivot 
            (
                 max(att_entry)
                for att_date in (' + @cols + ')
            ) p 
            union 
            SELECT idx, name, ' + @cols + ' from 
            (
                select idx,
                       name,
                    att_date,
                    att_out
                from test1
           ) x
            pivot 
            (
                 max(att_out)
                for att_date in (' + @cols + ')
            ) p '

execute(@query)

DBFiddle_Option1:

Option2:

You can create a view like this to bypass column incompatibility.

create view test2
as select idx, name as name, convert(varchar, att_date) as att_date,
convert(varchar, att_entry) as att_entry,
convert(varchar, att_out) as att_out,
convert(varchar, overtime) as overtime
from test1;

Then you can use this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.att_date) 
            FROM test2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT idx, name, ' + @cols + ' from 
            (
                select idx,
                       name,
                    att_date,
                    overtime
                from test2
           ) x
            pivot 
            (
                 max(overtime)
                for att_date in (' + @cols + ')
            ) p 
            union 
            SELECT idx, name, ' + @cols + ' from 
            (
                select idx,
                       name,
                    att_date,
                    att_out
                from test2
           ) x
            pivot 
            (
                 max(att_out)
                for att_date in (' + @cols + ')
            ) p 
            union 
            SELECT idx, name, ' + @cols + ' from 
            (
                select idx,
                       name,
                    att_date,
                    att_entry
                from test2
           ) x
            pivot 
            (
                 max(att_entry)
                for att_date in (' + @cols + ')
            ) p order by 3 desc'

execute(@query)