Merge the result of a query on the same line from the sequential

Asked

Viewed 1,584 times

0

I have the following appointment:

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

She 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 join the returns per person in the following way for example:

+---------+------+------------+---------------------+------+---------------------+------+---------------------+------+---------------------+------+
| 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 data of the person together on the same line.

Observing:

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

sql sql-server

  • It would not be possible to use the Group by in the column ID_PESS

  • Not because there will join and would not list the other fields date and sequential

  • Some questions, can Sequential be broken into two fields? One with the date and the other with the increment? This final number is generated as, from the application?

3 answers

1


Considering that the number of rows per person/date is variable, the ideal solution should employ the use of pivoted with dynamic SQL command. Here is a solution that works with 80 compatibility level (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;

0

Good afternoon.

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

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

0

@José Diz , I chose the one that was also a tip from you, because I needed to implement more things in the consultation and worked 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;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.