Group results in single line according to the sequence

Question

Group results in single line according to the sequence

Navigation

#1 by (3 votes)

0

I have the following query:

SELECT 
    * 
    FROM 
        ARELBATIDATRANSITOVIEW
        WHERE
        CHAPA = 1234
        AND CODEMP = 1
        AND BATIDA IS NOT NULL

It picks me up like this:

COD     |DATA                       |BATIDA                     |ST     |SEQ_BAT
1234    |2016-12-01 00:00:00.000    |2016-12-01 10:58:00.000    |C      |1
1234    |2016-12-01 00:00:00.000    |2016-12-01 13:56:00.000    |C      |2
1234    |2016-12-01 00:00:00.000    |2016-12-01 16:04:00.000    |C      |3
1234    |2016-12-01 00:00:00.000    |2016-12-01 21:44:00.000    |C      |4
1234    |2016-12-02 00:00:00.000    |2016-12-02 10:36:00.000    |C      |1
1234    |2016-12-02 00:00:00.000    |2016-12-02 14:29:00.000    |C      |2
1234    |2016-12-02 00:00:00.000    |2016-12-02 16:18:00.000    |C      |3
1234    |2016-12-02 00:00:00.000    |2016-12-02 21:18:00.000    |C      |4

I need the return to be as follows:

COD     |DATA                       |ST  |BATIDA1                 |BATIDA2                |BATIDA3                |BATIDA4 
1234    |2016-12-01 00:00:00.000    |C   |2016-12-01 10:58:00.000 |2016-12-01 13:56:00.000|2016-12-01 16:04:00.000|2016-12-01 21:44:00.000  
1234    |2016-12-02 00:00:00.000    |C   |2016-12-02 10:36:00.000 |2016-12-02 14:29:00.000|2016-12-02 16:18:00.000|2016-12-02 21:18:00.000

NOTE: What can be used as a base would be the SEQ_BAT example:

 BATIDA1 =[SEQ_BAT (1)]
 BATIDA2 =[SEQ_BAT (2)]
 BATIDA3 =[SEQ_BAT (3)]
 BATIDA4 =[SEQ_BAT (4)]

SQL SERVER 2008

sql sql-server

asked by anonymous 20.03.2017 / 16:37

1 answer

Problems with url using thymeleaf Save image coming from API in mobile memory

score 3 · Accepted Answer

Try

-- código #1 v2
SELECT COD, Cast(DATA as date) as DATA, ST, 
       BATIDA1= max(case when SEQ_BAT = 1 then Cast(BATIDA as time(0)) end),
       BATIDA2= max(case when SEQ_BAT = 2 then Cast(BATIDA as time(0)) end),
       BATIDA3= max(case when SEQ_BAT = 3 then Cast(BATIDA as time(0)) end),
       BATIDA4= max(case when SEQ_BAT = 4 then Cast(BATIDA as time(0)) end)
  from ARELBATIDATRANSITOVIEW
  where BATIDA is not null
        and CODEMP = 1
  group by COD, DATA, ST;

Another option is to use the relational operator PIVOT :

-- código #2 v3
with vABT as (
SELECT COD, Cast(DATA as date) as DATA, ST, SEQ_BAT,
       Cast(BATIDA as time(0)) as BATIDA
  from ARELBATIDATRANSITOVIEW 
  where BATIDA is not null
        and CODEMP = 1
)
SELECT COD, DATA, ST,  
       [1] as BATIDA1, [2] as BATIDA2, [3] as BATIDA3, [4] as BATIDA4  
  from vABT    
       pivot (max(BATIDA) for SEQ_BAT in ([1],[2],[3],[4])) as P;