Merge the result of a query on the same row from the sequence

0

I have the following query:

SELECT * 
FROM SERQUENCIAL 
WHERE DATA BETWEEN '2017-01-01' AND '2017-01-19'

It returns:

+--------------+-------+------------+-----------------------+
| ID_PESS NOME | DATA  | DATA_HORA  |      SEQUENCIAL       |
+--------------+-------+------------+-----------------------+
|         8788 | JOAO  | 2017-01-01 | 2017-01-01 13:01:01 1 |
|         8788 | JOAO  | 2017-01-01 | 2017-01-01 15:07:01 2 |
|         8788 | JOAO  | 2017-01-01 | 2017-01-01 17:08:01 3 |
|         8788 | JOAO  | 2017-01-01 | 2017-01-01 19:03:01 4 |
|         8533 | MARIA | 2017-01-03 | 2017-01-03 11:01:01 1 |
|         8533 | MARIA | 2017-01-03 | 2017-01-03 14:07:01 2 |
|         8533 | MARIA | 2017-01-03 | 2017-01-03 16:08:01 3 |
|         8533 | MARIA | 2017-01-03 | 2017-01-03 18:03:01 4 |
|         8935 | JOSE  | 2017-01-05 | 2017-01-05 13:01:01 1 |
|         8935 | JOSE  | 2017-01-05 | 2017-01-05 19:07:01 2 |
|         8935 | JOSE  | 2017-01-05 | 2017-01-05 20:08:01 3 |
|         8935 | JOSE  | 2017-01-05 | 2017-01-05 22:03:01 4 |
|         8935 | JOSE  | 2017-01-05 | 2017-01-05 23:03:01 5 |
+--------------+-------+------------+-----------------------+

How do I merge the returns per person as follows:

+---------+------+------------+---------------------+------+---------------------+------+---------------------+------+---------------------+------+
| ID_PESS | NOME |    DATA    |    DATA_HORA_01     | SEQ1 |    DATA_HORA_02     | SEQ2 |    DATA_HORA_03     | SEQ3 |    DATA_HORA_04     | SEQ4 |
+---------+------+------------+---------------------+------+---------------------+------+---------------------+------+---------------------+------+
|    8788 | JOAO | 2017-01-01 | 2017-01-01 13:01:01 |    1 | 2017-01-01 15:07:01 |    2 | 2017-01-01 17:08:01 |    3 | 2017-01-01 19:03:01 |    4 |
+---------+------+------------+---------------------+------+---------------------+------+---------------------+------+---------------------+------+

That is, the person's data together on the same line.

  

Note :

     

The person's data can be repeated on another day. If it happens, it will be   a new line of the person.

    
asked by anonymous 19.01.2017 / 11:09

3 answers

1

Considering that the number of lines per person / date is variable, the ideal solution should employ the use of pivoting with dynamic SQL command. This is a solution that works with compatibility level 80 (SQL Server 2000) onwards.

-- código #1 v4
-- define período de emissão
declare @DataInicial date, @DataFinal date;
set @DataInicial= Convert(date, '1/1/2017', 103);
set @DataFinal= Convert(date, '19/1/2017', 103);

-- obtém maior valor de sequencial
declare @maxSequencial int, @I int;
set @maxSequencial= (SELECT max(SEQUENCIAL) from tabela);

-- monta o comando sql dinâmico
declare @SQL nvarchar(4000);

set @SQL= N'SELECT ID_PESS, NOME, DATA, ';

set @I= 1;
while (@I <= @maxSequencial)
     begin
     set @SQL+= N'max(case when SEQUENCIAL = ' + 
                Cast(@I as nvarchar) + 
                N' then Cast(DATA_HORA as smalldatetime) end) as [' + 
                Cast(@I as nvarchar) + N'], ';
     set @I+= 1;
     end;
set @SQL= Left(@SQL, Len(@SQL) -1);     

set @SQL+= N' from tabela where DATA between @Data1 and @Data2' +
           N' group by ID_PESS, NOME, DATA;';
--
--print @SQL
Execute sp_executesql @stmt= @SQL, 
                      @params=N'@Data1 datetime, @Data2 datetime',
                      @Data1= @DataInicial, @Data2= @DataFinal;
    
19.01.2017 / 15:52
0

Good afternoon.

I would do the following: 1- would not leave the name of the employee / client / employee, but would use their ID (prevent the homonyms); and 2- You will do a search with INNER JOIN (I do not know how it works in SQL Server).

Remember that every sequential has an ID, and depending on the type of report you generate, it's worth having. For sorting, use an ORDER BY consisting of the ID (preventing homonyms) and the sequence number.

    
19.01.2017 / 16:44
0

@Joseph Diz,  I opted for this one that was also a hint of yours, because I needed to implement more things in the query and it turned out well with her. Thanks for the help.

-- código #2 v2
-- define período de emissão (formato dd/mm/aaaa)
declare @DataInicial date, @DataFinal date;
set @DataInicial= Convert(date, '1/1/2017', 103);
set @DataFinal= Convert(date, '19/1/2017', 103);

--
SELECT ID_PESS, NOME, DATA,
       max(case when SEQUENCIAL = 1 then Cast(DATA_HORA as smalldatetime) end) as [1],
       max(case when SEQUENCIAL = 2 then Cast(DATA_HORA as smalldatetime) end) as [2],
       max(case when SEQUENCIAL = 3 then Cast(DATA_HORA as smalldatetime) end) as [3],
       max(case when SEQUENCIAL = 4 then Cast(DATA_HORA as smalldatetime) end) as [4],
       max(case when SEQUENCIAL = 5 then Cast(DATA_HORA as smalldatetime) end) as [5],
       max(case when SEQUENCIAL > 5 then 'MAIS DE 5 DATAS!' else '' end) as [Obs]
  from tabela
  where DATA between @DataInicial and @DataFinal
  group by ID_PESS, NOME, DATA;
    
19.01.2017 / 17:41