It is necessary that the result of the query looked not so:

+------+------+-------+ | x | y | data | +------+------+-------+ | 3 | 3 | 172 | | 3 | 3 | 721 | | 3 | 3 | 652 | | 3 | 3 | 444 | | 3 | 3 | 781 | | 2 | 2 | 48 | | 2 | 2 | 27 | | 2 | 2 | 13 | | 2 | 2 | 16 | | 2 | 2 | 99 | | 4 | 4 | 4854 | | 4 | 4 | 2723 | | 4 | 4 | 1327 | | 4 | 4 | 1663 | | 4 | 4 | 9953 | | 5 | 5 | 48545 | | 5 | 5 | 27423 | | 5 | 5 | 12327 | | 5 | 5 | 16639 | | 5 | 5 | 99530 | | 1 | 1 | 4 | | 1 | 1 | 7 | | 1 | 1 | 3 | | 1 | 1 | 6 | | 1 | 1 | 9 | +------+------+-------+

and so:

 +------+------+-------+ | x | y | data | +------+------+-------+ | 3 | 3 | 172 | | | | 721 | | | 4 | 652 | | | | 444 | | | | 781 | | 2 | 2 | 48 | | | | 27 | | | | 13 | | | 3 | 16 | | | | 99 | | 4 | 4 | 4854 | | | | 2723 | | | | 1327 | | | 3 | 1663 | | | | 9953 | +------+------+-------+

Is this possible and, if so, how should the request look like?

  • Such a result should be in the console? Do not you think that this is a perversion? But I think that it is possible. But perverted. - alexlz
  • No, the result should not be in the console - I just wrote this for a better understanding of what I needed. I write a program in Delphi, I make a request there, the result should be written in Excel ... - GlamCity
  • Dig in the direction of GROUP BY - ReklatsMasters
  • And can you be more specific? - GlamCity
  • @glamcity then it is not even a perversion. If the picture with the result is not the real result of the query shown above, then go ahead. You read the result (whether there is a recordset or what) row by line, if x and / or y do not match the previous values, then transfer them to excel, and change the current value, if they match, then you don’t transfer (or clear the cell). - alexlz

1 answer 1

Here is for the initial question (if I understood it correctly):

 select case when @x = x then ' ' else ( @x := x) end x, case when @y = y then ' ' else ( @y := y) end y, data from t join (select @x := NULL, @y := NULL) r;

(initial 0 changed to NULL)

UPD

For the data from the question, I made a table

 create table t (x int, y int, data int);

entered the data (slightly corrected, so that the 'y' changed. Fulfilled the request. The results seemed satisfactory.

  (select @x := NULL, @y := NULL) r;

serves to set the initial values ​​of the variables @x and @y . Then in the query I compare the current values ​​of the field with the value of the variable, if they match, then the result is NULL (first there was a space ' ' ), they do not match - I assign the field value to the variable and give it to the result

  case when @x = x then ' ' else ( @x := x) end x

It was not possible to make the display of 'y' when changing the 'x'.

UPD2

Try this

 SELECT case when @fio = CONCAT(surname, " ", hunters.nm, " ", secnm) then "" else @fio := CONCAT(surname, " ", hunters.nm, " ", secnm) end AS "ФИО", CONCAT(huntcard_serial, " ", huntcard_number, " ", huntcard_who) AS huntcard, CONCAT(period_start, " - ", period_end) AS period, ',' places.nm AS place, animals.nm AS animal, closed FROM hunters, permission, permission_animals, animals, places, (select @fio := NULL) r WHERE permission_animals.id_animal=animals.id AND hunters.id=permission.id_hunter AND permission.id=permission_animals.id_permission AND place=places.id AND hunters.id='+IntToStr(user_report_id)
  • In my opinion, misunderstood. I just don't want to display the same values ​​in the columns ... - GlamCity
  • I do not understand you now. Are the same values ​​in the x, y columns displayed? (Well, with y there can be a situation when x changes, and y remains the same). If displayed, we will understand. Yes, instead of spaces, the NULL value is more suitable, I don’t know this TJvgExportExcel. - alexlz
  • I didn’t quite understand what to do with this query - should I enter my own tables and fields in it or what? - GlamCity
  • Corrected the answer - alexlz
  • Tell me, what will it look like in my example? - GlamCity