SQL command return [duplicate]

Question

SQL command return [duplicate]

Navigation

#1 by (4 votes)

5

I need to select in my database all printers that are compatible with a particular selected supply, for example, the 155 code supply that is called 50FOZ00 . I make the query below to select all supported 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, this being displayed to me:

Can this return be 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

asked by anonymous 02.08.2017 / 14:27

1 answer

How to publish site made in Visual Studio? Bar chart sorted using dplyr and ggplot2

score 4 · Accepted Answer

I made an example here, but I was not able to 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 chat, it is not necessary for all models to come in separate columns, and a solution with arrays is possible. Here is a code example:

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 it in SQLFiddle: link

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