SQL query in out time attendance each days

Question

I'm a newbie in SQL Server; I have trouble to write this query.

This is my sample data:

   | Id |   date    |    time    |
   +----+-----------+------------+
   | 1  | 2020-11-1 | 07:30:00   |
   | 1  | 2020-11-1 | 15:50:00   |
   | 2  | 2020-11-1 | 07:30:00   |
   | 2  | 2020-11-1 | 15:54:00   |
   | 1  | 2020-11-2 | 07:31:00   |
   | 1  | 2020-11-2 | 15:46:00   |

This is the result I need:

| id |in_2020_11_01 | out_2020_11_01  |  in_2020_11_02 | out_2020_11_02 |
+----+--------------+-----------------+----------------+----------------+
| 1  |  07:30:00    |   15:50:00      |   07:31:00     |    15:46:00    |
| 2  |  07:30:00    |   15:54:00      |   00:00:00     |    00:00:00    |

How can I build the result? Is it possible to get a result like that?

Thanks in advance

Answer 1

You need to use dynamic T-SQL and build a PIVOT statement. Here is full working example:

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [Id] INT
   ,[date] DATE
   ,[time] TIME
);

INSERT INTO #DataSource ([Id], [date], [time])
VALUES (1, '2020-11-1', '07:30:00')
      ,(1, '2020-11-1', '15:50:00')
      ,(2, '2020-11-1', '07:30:00')
      ,(2, '2020-11-1', '15:54:00')
      ,(1, '2020-11-2', '07:31:00')
      ,(1, '2020-11-2', '15:46:00');

DECLARE @ColumnsPIVOT VARCHAR(MAX)
       ,@ColumnsSELECT VARCHAR(MAX)
       ,@DyanmicTSQLSTatement NVARCHAR(MAX);

WITH DataSource ([date], [prefix], [column_name]) AS
(
    SELECT DISTINCT [date]
                   ,[prefix]
                   ,[prefix] + '_' + REPLACE([date], '-', '_')
    FROM #DataSource
    CROSS APPLY
    (
        VALUES ('in')
              ,('out')
    ) DS ([prefix])
)
SELECT @ColumnsPIVOT = STUFF
                        (
                            (
                                SELECT ',' + QUOTENAME([column_name])
                                FROM DataSource
                                ORDER BY [date]
                                        ,[prefix]
                                FOR XML PATH(''), TYPE
                            ).value('.', 'VARCHAR(MAX)')
                            ,1
                            ,1
                            ,''
                        )
      ,@ColumnsSELECT = STUFF
                        (
                            (
                                SELECT ', ISNULL(' + QUOTENAME([column_name]) + ', ''00:00:00'') AS ' + QUOTENAME([column_name])
                                FROM DataSource
                                ORDER BY [date]
                                        ,[prefix]
                                FOR XML PATH(''), TYPE
                            ).value('.', 'VARCHAR(MAX)')
                            ,1
                            ,1
                            ,''
                        );


    -- SQL Server 2017+
    /*
    WITH DataSource ([date], [prefix], [column_name]) AS
    (
        SELECT DISTINCT [date]
                       ,[prefix]
                       ,[prefix] + '_' + REPLACE([date], '-', '_')
        FROM #DataSource
        CROSS APPLY
        (
            VALUES ('in')
                  ,('out')
        ) DS ([prefix])
    )
    SELECT @ColumnsPIVOT = STRING_AGG(QUOTENAME([column_name]), ',') WITHIN GROUP(ORDER BY [date], [prefix])
          ,@ColumnsSELECT = STRING_AGG('ISNULL(' + QUOTENAME([column_name]) + ', ''00:00:00'') AS ' + QUOTENAME([column_name]), ',') WITHIN GROUP(ORDER BY [date], [prefix])
    FROM DataSource;
    */
    
SET @DyanmicTSQLSTatement= 'SELECT [ID], ' + @ColumnsSELECT + '
                            FROM
                            (
                                SELECT [id]
                                      ,CASE ROW_NUMBER() OVER (PARTITION BY [ID], [Date] ORDER BY [Time])
                                            WHEN 1 THEN ''in''
                                            WHEN 2 THEN ''out''
                                       END + ''_'' + REPLACE([date], ''-'', ''_'')
                                      ,[time]
                                FROM #DataSource
                            ) DS ([id], [date], [time])
                            PIVOT
                            (
                                MAX([time]) FOR [date] IN (' + @ColumnsPIVOT +')
                            ) PVT';

EXEC sp_executesql @DyanmicTSQLSTatement;