Home / Questions / 146659
Accepted
SeWaNsX
Asked: 2020-03-16 13:00:32 +0800 CST 2020-03-16 13:00:32 +0800 CST

Transpose data with 2 columns (PIVOT)

  • 772

People, I turn to you because google has not been very helpful for what I need.

I am looking for a way to make a dynamic pivot with 2 columns. Example:

I have this:

id  rut         fecha                   entrada salida
2   15114999    2017-10-16 00:00:00.000 9:00      :  
626 15114999    2017-10-16 00:00:00.000 11:00   13:00
627 15114999    2017-10-16 00:00:00.000 13:30   14:00
628 15114999    2017-10-16 00:00:00.000 15:00   16:00
629 15114999    2017-10-16 00:00:00.000 16:20   16:30
630 15114999    2017-10-16 00:00:00.000 17:00   17:30
631 15114999    2017-10-16 00:00:00.000 18:00   19:00
3   15114999    2017-10-17 00:00:00.000 9:00    22:00
4   15114999    2017-10-18 00:00:00.000 9:00    14:00

and I require this result

rut         fecha                   entrada salida entrada2 salida2 entrada3 salida3 entrada4 salida4 entrada5 salida5 entrada6 salida6 entrada7 salida7
15114999    2017-10-16 00:00:00.000 9:00      :     11:00   13:00   13:30   14:00   15:00   16:00   16:20   16:30   17:00   17:30   18:00   19:00
15114999    2017-10-17 00:00:00.000 9:00    22:00                                               
15114999    2017-10-18 00:00:00.000 9:00    14:00

The id is not correlative.

sql

1 Answers

  1. Best Answer

    I quickly did this little example, what I did was I gave the outputs and inputs an order number, then I pivoted them separately based on that number. In the end I joined the two tables with a join and grouped them by date.

    I know it's not very nice to say but I hope it can be of some use to you. Greetings.

    create table #tabla1(
    id int,
    rut varchar(15),     
    fecha date,                   
    entrada     time,
    salida  time
);

insert into #tabla1(
    id,
    rut,
    fecha,               
    entrada,
    salida
)values 
(2, '15114999',      '2017-10-16 00:00:00.000', '9:00', ''),
(626 ,'15114999',    '2017-10-16 00:00:00.000', '11:00',   '13:00'),
(627 ,'15114999',    '2017-10-16 00:00:00.000', '13:30',   '14:00'),
(628 ,'15114999',    '2017-10-16 00:00:00.000', '15:00',   '16:00'),
(629 ,'15114999',    '2017-10-16 00:00:00.000', '16:20',   '16:30'),
(630 ,'15114999',    '2017-10-16 00:00:00.000', '17:00',   '17:30'),
(631 ,'15114999',    '2017-10-16 00:00:00.000', '18:00',   '19:00'),
(3   ,'15114999',    '2017-10-17 00:00:00.000', '9:00',    '22:00'),
(4   ,'15114999',    '2017-10-18 00:00:00.000', '9:00',    '14:00');


declare @columnsEntradas as varchar(max);
declare @columnsSalidas as varchar(max);
declare @maxEntradas as varchar(max);
declare @maxSalidas as varchar(max);
declare @query as varchar(max);

SET @columnsEntradas = stuff((
    SELECT DISTINCT ', ' + 'entrada' + cast (row_number() over (partition by fecha order by entrada asc) as varchar(2))
    FROM #tabla1
    FOR XML PATH('')
    ), 1, 2, '');    
SET @columnsSalidas = stuff((
    SELECT DISTINCT ', ' + 'salida' + cast (row_number() over (partition by fecha order by entrada asc) as varchar(2))
    FROM #tabla1
    FOR XML PATH('')
    ), 1, 2, '');
SET @maxEntradas = stuff((
    SELECT DISTINCT ', ' + 'MAX('+'entrada' + cast (row_number() over (partition by fecha order by entrada asc) as varchar(2))+')'+ 
        'as entrada' + cast (row_number() over (partition by fecha order by entrada asc) as varchar(2))
    FROM #tabla1
    FOR XML PATH('')
    ), 1, 2, '');
SET @maxSalidas = stuff((
    SELECT DISTINCT ', ' + 'MAX(' + 'salida' + cast (row_number() over (partition by fecha order by salida asc) as varchar(2))+')'+
        'as salida' + cast (row_number() over (partition by fecha order by salida asc) as varchar(2))
    FROM #tabla1
    FOR XML PATH('')
    ), 1, 2, '');

set @query= 'with cte1 as (
    select * from (
    select id, rut, fecha, entrada, ''entrada'' + cast (row_number() over (partition by fecha order by entrada asc) as varchar(2)) as orden
        from #tabla1
    ) as src 
    pivot( max(entrada) for orden in  ('+@columnsEntradas+') ) piv1
), cte2 as (
    select * from (
        select id, rut, fecha, salida,''salida'' + cast (row_number() over (partition by fecha order by salida asc) as varchar(2)) as orden
        from #tabla1
    ) as src2 
pivot( max(salida) for orden in ('+@columnsSalidas+') ) piv1
) select t1.fecha, '+@maxEntradas+', '+@maxSalidas+'
from cte1  as t1
inner join cte2 as t2 on t1.id = t2.id 
group by t1.fecha
'

execute(@query);
drop table #tabla1;
    • 2