|
ActiveDelphi .: O site do programador Delphi! :.
|
Exibir mensagem anterior :: Exibir próxima mensagem |
Autor |
Mensagem |
adriano_servitec Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004 Mensagens: 17618
|
Enviada: Qui Jan 25, 2018 4:06 pm Assunto: Colocar titulos no select usando postgresql? |
|
|
Como coloco o nome da primeira coluna deste select em cima e os seus conteudos embaixo de cada um deles neste union
Estou usando postgres
Exemplo PRODUTOS primeira linha e na sequencia seus produtos ai no próximo union a TRANSPORTADORA e seus conteúdos, etc...
Código: | select
'PRODUTOS',
row_number() over() as ordem,
descricao,
'' as tipo_conta,
'' as agencia,
'' as conta,
'' as titular,
'' as titular_cpfcnpj
from fornecedor_produto
where id_fornecedor = 412884
union all
select
'TRANSPORTADORAS',
row_number() over() as ordem,
descricao,
'' as tipo_conta,
'' as agencia,
'' as conta,
'' as titular,
'' as titular_cpfcnpj
from fornecedor_transportadora
where id_fornecedor = 412884
union all
select
'DADOS BANCÁRIOS',
row_number() over() as ordem,
b.descricao,
fb.tipo_conta,
fb.agencia,
fb.conta,
fb.titular,
fb.titular_cpfcnpj
from fornecedor_banco fb
inner join banco b on fb.id_banco = cast(b.id as int)
where fb.id_fornecedor = 412884
group by
b.descricao,
fb.tipo_conta,
fb.agencia,
fb.conta,
fb.titular,
fb.titular_cpfcnpj |
_________________ Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
Voltar ao Topo |
|
|
joemil Moderador
Registrado: Quinta-Feira, 25 de Março de 2004 Mensagens: 9098 Localização: Sinop-MT
|
Enviada: Sex Jan 26, 2018 7:43 am Assunto: |
|
|
Código: | select
'PRODUTOS' AS titulo,
...
...
union all
select
'TRANSPORTADORAS' AS titulo,
...
... |
_________________ <b>SEMPRE COLOQUE [RESOLVIDO] NO SEU POST</b>
Enviar imagens: http://tinypic.com/ |
|
Voltar ao Topo |
|
|
adriano_servitec Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004 Mensagens: 17618
|
Enviada: Sex Jan 26, 2018 7:46 am Assunto: |
|
|
joemil escreveu: | Código: | select
'PRODUTOS' AS titulo,
...
...
union all
select
'TRANSPORTADORAS' AS titulo,
...
... |
|
Não é bem isso que estou buscando amigo.
Quero ver se da pra sair na grade
primeira linha o titulo
e a segunda linha adiante os conteúdos do banco
ai no próximo union alll a mesma coisa
Acho que o WITH pode resolver meu problema, mais não sei como fazer a sintaxe
Hj esta assim meu query
Código: | SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_produto
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_transportadora
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
row_number() over() as ordem,
b.descricao,
CAST((
CASE
WHEN
fb.tipo_conta = 'C'
then
'C / Corrente'
WHEN
fb.tipo_conta = 'P'
then
'Poupança'
END
) as character varying(60)) as tipo_conta,
CAST(fb.agencia as char(6)),
CAST(fb.conta as char(10)),
CAST(fb.titular as char(60)),
CAST(fb.titular_cpfcnpj as char(20))
FROM
fornecedor_banco fb
INNER JOIN
banco b
on fb.id_banco = cast(b.id as int)
WHERE
fb.id_fornecedor = '412884'
GROUP BY
b.descricao, fb.tipo_conta, fb.agencia, fb.conta, fb.titular, fb.titular_cpfcnpj |
_________________ Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
Voltar ao Topo |
|
|
imex Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011 Mensagens: 11666
|
Enviada: Sex Jan 26, 2018 10:07 am Assunto: |
|
|
Bom dia,
Não sei se entendi corretamente mas experimente fazer uns testes dessa forma:
Código: | with
CTE_Produto as
(
SELECT
'PRODUTOS' as descricao
UNION ALL
SELECT
descricao,
FROM
fornecedor_produto
WHERE
id_fornecedor = '412884'
),
CTE_Transportadora as
(
SELECT
'TRANSPORTADORAS' as descricao
UNION ALL
SELECT
descricao,
FROM
fornecedor_transportadora
WHERE
id_fornecedor = '412884'
),
CTE_Banco as
(
SELECT
'DADOS BANCÁRIOS' as descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
b.descricao,
CAST((
CASE
WHEN
fb.tipo_conta = 'C'
then
'C / Corrente'
WHEN
fb.tipo_conta = 'P'
then
'Poupança'
END
) as character varying(60)) as tipo_conta,
CAST(fb.agencia as char(6)),
CAST(fb.conta as char(10)),
CAST(fb.titular as char(60)),
CAST(fb.titular_cpfcnpj as char(20))
FROM
fornecedor_banco fb
INNER JOIN
banco b
on fb.id_banco = cast(b.id as int)
WHERE
fb.id_fornecedor = '412884'
GROUP BY
b.descricao, fb.tipo_conta, fb.agencia, fb.conta, fb.titular, fb.titular_cpfcnpj
)
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
CTE_Produto
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
CTE_Transportadora
UNION ALL
SELECT
row_number() over() as ordem,
*
FROM
CTE_Banco |
Espero que ajude
_________________
Assinatura: https://www.imoveisemexposicao.com.br/imoveis-alugar-guarulhos-residencial-apartamento |
|
Voltar ao Topo |
|
|
adriano_servitec Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004 Mensagens: 17618
|
Enviada: Sex Jan 26, 2018 10:45 am Assunto: |
|
|
imex escreveu: | Bom dia,
Não sei se entendi corretamente mas experimente fazer uns testes dessa forma:
Código: | with
CTE_Produto as
(
SELECT
'PRODUTOS' as descricao
UNION ALL
SELECT
descricao,
FROM
fornecedor_produto
WHERE
id_fornecedor = '412884'
),
CTE_Transportadora as
(
SELECT
'TRANSPORTADORAS' as descricao
UNION ALL
SELECT
descricao,
FROM
fornecedor_transportadora
WHERE
id_fornecedor = '412884'
),
CTE_Banco as
(
SELECT
'DADOS BANCÁRIOS' as descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
b.descricao,
CAST((
CASE
WHEN
fb.tipo_conta = 'C'
then
'C / Corrente'
WHEN
fb.tipo_conta = 'P'
then
'Poupança'
END
) as character varying(60)) as tipo_conta,
CAST(fb.agencia as char(6)),
CAST(fb.conta as char(10)),
CAST(fb.titular as char(60)),
CAST(fb.titular_cpfcnpj as char(20))
FROM
fornecedor_banco fb
INNER JOIN
banco b
on fb.id_banco = cast(b.id as int)
WHERE
fb.id_fornecedor = '412884'
GROUP BY
b.descricao, fb.tipo_conta, fb.agencia, fb.conta, fb.titular, fb.titular_cpfcnpj
)
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
CTE_Produto
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
CTE_Transportadora
UNION ALL
SELECT
row_number() over() as ordem,
*
FROM
CTE_Banco |
Espero que ajude
_________________
Assinatura: https://www.imoveisemexposicao.com.br/imoveis-alugar-guarulhos-residencial-apartamento |
Perfeito Imex, mais se desse para colocar a ordem do inicio dos dados seria melhor em vez de iniciar a ordem no titulo _________________ Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
Voltar ao Topo |
|
|
imex Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011 Mensagens: 11666
|
Enviada: Sex Jan 26, 2018 11:04 am Assunto: |
|
|
Experimente dessa forma:
Código: | SELECT
CAST(NULL as integer) as ordem,
'PRODUTOS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_produto
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
CAST(NULL as integer) as ordem,
'TRANSPORTADORAS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_transportadora
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
CAST(NULL as integer) as ordem,
'DADOS BANCÁRIOS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
b.descricao,
CAST((
CASE
WHEN
fb.tipo_conta = 'C'
then
'C / Corrente'
WHEN
fb.tipo_conta = 'P'
then
'Poupança'
END
) as character varying(60)) as tipo_conta,
CAST(fb.agencia as char(6)),
CAST(fb.conta as char(10)),
CAST(fb.titular as char(60)),
CAST(fb.titular_cpfcnpj as char(20))
FROM
fornecedor_banco fb
INNER JOIN
banco b
on fb.id_banco = cast(b.id as int)
WHERE
fb.id_fornecedor = '412884'
GROUP BY
b.descricao, fb.tipo_conta, fb.agencia, fb.conta, fb.titular, fb.titular_cpfcnpj |
Espero que ajude |
|
Voltar ao Topo |
|
|
adriano_servitec Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004 Mensagens: 17618
|
Enviada: Sex Jan 26, 2018 12:02 pm Assunto: |
|
|
imex escreveu: | Experimente dessa forma:
Código: | SELECT
CAST(NULL as integer) as ordem,
'PRODUTOS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_produto
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
CAST(NULL as integer) as ordem,
'TRANSPORTADORAS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
descricao,
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
FROM
fornecedor_transportadora
WHERE
id_fornecedor = '412884'
UNION ALL
SELECT
CAST(NULL as integer) as ordem,
'DADOS BANCÁRIOS' as descricao
CAST('' as char(60)) as tipo_conta,
CAST('' as char(6)) as agencia,
CAST('' as char(10)) as conta,
CAST('' as char(60)) as titular,
CAST('' as char(20)) as titular_cpfcnpj
UNION ALL
SELECT
row_number() over() as ordem,
b.descricao,
CAST((
CASE
WHEN
fb.tipo_conta = 'C'
then
'C / Corrente'
WHEN
fb.tipo_conta = 'P'
then
'Poupança'
END
) as character varying(60)) as tipo_conta,
CAST(fb.agencia as char(6)),
CAST(fb.conta as char(10)),
CAST(fb.titular as char(60)),
CAST(fb.titular_cpfcnpj as char(20))
FROM
fornecedor_banco fb
INNER JOIN
banco b
on fb.id_banco = cast(b.id as int)
WHERE
fb.id_fornecedor = '412884'
GROUP BY
b.descricao, fb.tipo_conta, fb.agencia, fb.conta, fb.titular, fb.titular_cpfcnpj |
Espero que ajude | Perfeito Imex. Muito obrigado amigo.
Grande DBA _________________ Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
Voltar ao Topo |
|
|
|
|
Enviar Mensagens Novas: Proibido. Responder Tópicos Proibido Editar Mensagens: Proibido. Excluir Mensagens: Proibido. Votar em Enquetes: Proibido.
|
|