Return of SQL command

Asked

Viewed 164 times

5

I need to select in my database all printers compatible with a selected supply, for example, the code supply 155 who is called 50FOZ00. I do the query below to select all compatible printers:

SELECT suprimento
      ,modelo
  FROM public.suprimento
      ,public.modelosuprimento
      ,public.impressoramodelo
 WHERE public.suprimento.codigo = public.modelosuprimento.codigosuprimento
   AND public.modelosuprimento.codigomodelo = public.impressoramodelo.codigo
   AND public.suprimento.codigo = 155

And my return is coming correctly, being shown me that:

inserir a descrição da imagem aqui

Is it possible that this return is displayed differently? Where in the first column is the supply and for each compatible printer model a new column is created? Getting something similar to this:

SUPRIMENTO | MODELO | MODELO | MODELO | MODELO | MODELO | MODELO | MODELO | MODELO | MODELO | MODELO |
50FOZ00    | MS310  | MS312  | MS315  | MS410  | MS415  | MS610  | MX310  | MX410  | MX511  | MX611  |
sql postgresql
  • 1

    there’s a guy named PIVOT

  • You could give an example @Marconciliosouza??

  • With PIVOT, would have to pass the query the codes of the fixed supply, can be R.Santos?

  • It may be, because in the future I will need a parameter (code) to select only a certain supply

  • You would have some example to give me @David?

  • @David, @Marconciliosouza group by to display all printers by supply? I tried so: select suprimento, modelo from public.suprimento, public.modelosuprimento, public.impressoramodelo where public.suprimento.codigo = public.modelosuprimento.codigosuprimento
and public.modelosuprimento.codigomodelo = public.impressoramodelo.codigo and public.suprimento.codigo = 155 group by public.suprimento.suprimento but failed to execute

  • 1

    @Marconciliosouza I was researching now the subject you suggested "PIVOT" but this way I do not have to determine the columns dynamically

  • @Renan I could not understand the example you suggested as a possible duplicate, and the author of the question also did not select any of the answers as correct for it and for this reason I created my

  • There is a way to do this dynamically, creating a function that returns a table (I don’t know if Postgresql has this feature), and within the function does the query that returns the supply, which then stops for the pivot this data, but gives a good job, until today I did not have time to do it, using oracle, but it is the tip ;)

Show 4 more comments

1 answer

4


I made an example here, but I also could not dynamically inform the number of columns. I hope it helps:

create table suprimentos (
codigo varchar(20),
modelo varchar(20));


insert into suprimentos (codigo,modelo) values ('50F0Z000','MS310');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS312');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS315');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS317');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS318');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS325');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS369');
insert into suprimentos (codigo,modelo) values ('50F0Z000','MS323');



  select * from crosstab(
'select codigo,''compativel'', modelo from suprimentos order by 1,2'::text
) as ct (codigo varchar, suprimento varchar, suprimento2 varchar)

Edit:

As discussed in the chat, it is not necessary that all templates come in separate columns, being possible a solution with arrays. Follow example of code:

select codigo, array_agg(modelo) as compativeis from suprimentos group by codigo;

or 

select codigo, array_to_string(array_agg(modelo),' / ') as compativeis from suprimentos group by codigo;

I put in Sqlfiddle: http://sqlfiddle.com/#! 17/ced2f/4

Your Query would look like this:

SELECT suprimento,
       array_to_string(array_agg(modelo),' / ') AS modelo
  FROM public.suprimento
      ,public.modelosuprimento
      ,public.impressoramodelo
 WHERE public.suprimento.codigo = public.modelosuprimento.codigosuprimento
   AND public.modelosuprimento.codigomodelo = public.impressoramodelo.codigo
   AND public.suprimento.codigo = 155
 GROUP BY suprimento

  • i tested your example and I was shown the following error: ERROR: function crosstab(text) does not exist
LINE 1: select * from crosstab(
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function crosstab(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 15

  • which version of the bank is using ?

  • It is Postgresql 9.6

  • try to run the command seguitne: CREATE EXTENSION tablefunc

  • Now it has run, the problem is that just as you commented I can’t dynamically determine the amount of columns there itself it does not solve my problem, but thanks for the help +1

  • yes, but at least it already gives a light, I will follow the question to learn too, vlw

  • Let’s go continue this discussion in chat.

  • can I place more than two items in the array? For example array_to_string(array_agg(modelo),' / ',(codigomodelo)) AS modelo

  • @R.Santos you can concatenate the columns before placing them in the array, array_to_string(array_agg(modelo || ',' || codigomodelo ),' / ') as modelo

  • Perfect @Rovannlinhalis, thank you so much

  • Last thing, I can determine one limit for the array to be displayed? so as not to keep repeating? Or soon not to allow repeated data to be added to the array

  • 1

    I figured out how, I used it array_agg(distinct(..))

Show 7 more comments

Browser other questions tagged sql postgresql

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