Oracle - Como exibir o resultado de um Select em uma unica Linha?

Assuntos Gerais
#1

Pessoal tem como colocar o resultado de um select concatenado em uma unica linha por um separador qualquer, sem precisar criar uma function

alguma palavra reservada no select que faca isso de maneira mais facil?

[]'s

#2

vou tentar explicar ± a situacao:

tenho uma tabela de Itens e uma outra tabela com as caracteristicas dos itens.

por exemplo: um item caneta com o codigo x tem as caracteristicas ‘escrita fina’, esferografica’, ‘tinta azul’ etc …

preciso fazer um select de itens, mas eu quero retornar junto com o item todas as caracteristicas dele numa unica linha separados por um caractere qualquer

#3

Cara usa um cursor que resolve, declara uma variavel e vai concatenando o valor de cada registro nela.

#4

eu queria fazer sem usar funcoes definidas pelo usuario

SELECT SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(VLR_CARACTERISTICA_ITEM_CIT, '/'), '/', ', ')), 2) COLUNA FROM (select CIT.*, ROW_NUMBER() OVER(PARTITION BY CIT.FK_SEQ_ITEM_MATERIAL_IMA ORDER BY CIT.FK_SEQ_ITEM_MATERIAL_IMA) ROW# from adm_gestaomaterial.tb_caracteristica_item_cit CIT left join adm_gestaomaterial.tb_material_caracteristica_mca MCA on MCA.pk_seq_mat_caracteristica_mca = CIT.fk_seq_mat_caracteristica_mca left join adm_gestaomaterial.tb_caracteristica_car CAR on CAR.pk_seq_caracteristica_car = MCA.fk_seq_caracteristica_car where MCA.dsc_status_mca = 'A' AND CIT.fk_seq_item_material_ima = 9 ) START WITH ROW# = 1 CONNECT BY PRIOR FK_SEQ_ITEM_MATERIAL_IMA = FK_SEQ_ITEM_MATERIAL_IMA AND PRIOR ROW# = ROW# - 1 GROUP BY FK_SEQ_ITEM_MATERIAL_IMA

esse select ta dando um erro na funcao SYS_CONNECT_BY_PATH dizendo que um separador nao pode fazer parte da coluna … alguem sabe porque eh?

obs:

tentei juntar esses dois selects

SELECT SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(NOM_CATEGORIA_CAT, '/'), '/', ', ')), 2) Concatenated_String FROM (select A.*, row_number() OVER(Partition by FK_SEQ_USUARIO_USU order by FK_SEQ_USUARIO_USU) ROW# from adm_gestaomaterial.tb_categoria_cat A) START WITH ROW# = 1 CONNECT BY PRIOR FK_SEQ_USUARIO_USU = FK_SEQ_USUARIO_USU AND PRIOR row# = row# - 1 GROUP BY FK_SEQ_USUARIO_USU

esse aqui esta funcionando me retorna uma unica linha da tabela com os valores concatenados por virgula (Select de teste que eu usei para testar concatenacao)

e esse

select CIT.vlr_caracteristica_item_cit as caracteristica, CIT.FK_SEQ_ITEM_MATERIAL_IMA from adm_gestaomaterial.tb_caracteristica_item_cit CIT left join adm_gestaomaterial.tb_material_caracteristica_mca MCA on MCA.pk_seq_mat_caracteristica_mca = CIT.fk_seq_mat_caracteristica_mca left join adm_gestaomaterial.tb_caracteristica_car CAR on CAR.pk_seq_caracteristica_car = MCA.fk_seq_caracteristica_car where MCA.dsc_status_mca = 'A' AND CIT.fk_seq_item_material_ima = 9
select que me retorna todas as caracteristicas do material com cod = 9

edit
no site da oracle diz para mudar para um separador que nao aparece nas colunas, mas eu ja tentei varios caracteres que nao estao aparecendo nas colunas e nada funciona

#5

aproveitando o embalo alguem sabe como calcular horas no oracle? tipo tenho 2 campos varchars um com a hra inicio e outro com a hra termino… e preciso fazer uma query para trazer a diferença entre elas ou seja hra fim - hra inicio… como fazer isto direto na query?

#6

[quote=luistiagos]aproveitando o embalo alguem sabe como calcular horas no oracle? tipo tenho 2 campos varchars um com a hra inicio e outro com a hra termino… e preciso fazer uma query para trazer a diferença entre elas ou seja hra fim - hra inicio… como fazer isto direto na query?
[/quote]

tenho para mim que voce soh precisa diminuir as duas datas … se nao me engano o oracle da o resutlado em dias, se voce quiser passar isso para horas basta multiplicar por 24

#7

mas o campo tem que ser do tipo data, eh soh usar a funcao TO_DATE

#8

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(VLR_CARACTERISTICA_ITEM_CIT,'--')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS COLUNA FROM (SELECT CIT.*, ROW_NUMBER() OVER (PARTITION BY CIT.FK_SEQ_ITEM_MATERIAL_IMA ORDER BY CIT.FK_SEQ_ITEM_MATERIAL_IMA) AS curr, ROW_NUMBER() OVER (PARTITION BY CIT.FK_SEQ_ITEM_MATERIAL_IMA ORDER BY CIT.FK_SEQ_ITEM_MATERIAL_IMA) -1 AS prev FROM ADM_GESTAOMATERIAL.TB_CARACTERISTICA_ITEM_CIT CIT LEFT JOIN ADM_GESTAOMATERIAL.TB_MATERIAL_CARACTERISTICA_MCA MCA ON MCA.PK_SEQ_MAT_CARACTERISTICA_MCA = CIT.FK_SEQ_MAT_CARACTERISTICA_MCA LEFT JOIN ADM_GESTAOMATERIAL.TB_CARACTERISTICA_CAR CAR ON CAR.PK_SEQ_CARACTERISTICA_CAR = MCA.FK_SEQ_CARACTERISTICA_CAR WHERE MCA.DSC_STATUS_MCA = 'A' AND CIT.FK_SEQ_ITEM_MATERIAL_IMA = 15) GROUP BY FK_SEQ_ITEM_MATERIAL_IMA CONNECT BY prev = PRIOR curr AND FK_SEQ_ITEM_MATERIAL_IMA = PRIOR FK_SEQ_ITEM_MATERIAL_IMA START WITH curr = 1;

essa aqui nao deu defeito, vou fazer uns testes soh para ter certeza que esta me retornando o resultado certo

#9

agora uma outra duvida.

SELECT IMA.PK_SEQ_ITEM_MATERIAL_IMA AS ID_IMS, (SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(VLR_CARACTERISTICA_ITEM_CIT, '; ')) KEEP(DENSE_RANK LAST ORDER BY curr), '; ') AS COLUNA FROM (SELECT CIT.*, ROW_NUMBER() OVER(PARTITION BY CIT.FK_SEQ_ITEM_MATERIAL_IMA ORDER BY CIT.FK_SEQ_ITEM_MATERIAL_IMA) AS curr, ROW_NUMBER() OVER(PARTITION BY CIT.FK_SEQ_ITEM_MATERIAL_IMA ORDER BY CIT.FK_SEQ_ITEM_MATERIAL_IMA) - 1 AS prev FROM ADM_GESTAOMATERIAL.TB_CARACTERISTICA_ITEM_CIT CIT LEFT JOIN ADM_GESTAOMATERIAL.TB_MATERIAL_CARACTERISTICA_MCA MCA ON MCA.PK_SEQ_MAT_CARACTERISTICA_MCA = CIT.FK_SEQ_MAT_CARACTERISTICA_MCA LEFT JOIN ADM_GESTAOMATERIAL.TB_CARACTERISTICA_CAR CAR ON CAR.PK_SEQ_CARACTERISTICA_CAR = MCA.FK_SEQ_CARACTERISTICA_CAR WHERE MCA.DSC_STATUS_MCA = 'A' AND CIT.FK_SEQ_ITEM_MATERIAL_IMA = IMA.PK_SEQ_ITEM_MATERIAL_IMA) GROUP BY FK_SEQ_ITEM_MATERIAL_IMA CONNECT BY prev = PRIOR curr AND FK_SEQ_ITEM_MATERIAL_IMA = PRIOR FK_SEQ_ITEM_MATERIAL_IMA START WITH curr = 1) AS DSC_IMS FROM ADM_GESTAOMATERIAL.TB_ITEM_MATERIAL_IMA IMA WHERE IMA.DSC_STATUS_IMA = 'A' group by IMA.PK_SEQ_ITEM_MATERIAL_IMA

nas linhas 15-16 estou tentando fazer com que o select interno compare o valor de um campo com o valor de um campo do select externo, mas ele esta retornando uma msg de erro dizendo que IMA.PK_SEQ_ITEM_MATERIAL_IMA eh um identificador invalido.

como eu procederia para efetuar essa comparacao?

[]'s

#10

Vc já tentou o pipe “||”?

#11

além disso usa outro separador… # ou ! pensa em outro

#12

Dá uma olhada nesses site:

http://www.oracle.com/technology/oramag/code/tips2006/101606.html
http://www.dbasupport.com/forums/showthread.php?t=47760

Fiz um select genérico aqui de um q funciona ok no oracle 10g:


SELECT T2.CAMPO1, T2.CAMPO2, T2.CAMPO3
					
	SUBSTR(
	MAX(REPLACE(
	SYS_CONNECT_BY_PATH(SIGLA, '/')
	,'/',', ')),2) AS SIGLA
	
	FROM 
	
	(SELECT T.*, 
	
	CASE WHEN ROW# = 13 THEN 
		 SIGLA || ', ...' 
	ELSE 
		 SIGLA || ''
                 
	END SIGLA FROM ( SELECT 
        T2.CAMPO1,
        T2.CAMPO2,
        T2.CAMPO3,
	T1.SIGLA,
	ROW_NUMBER() OVER (PARTITION BY T2.CAMPO1, T2.CAMPO2, T2.CAMPO3  
	ORDER BY T2.CAMPO1, T2.CAMPO2, T2.CAMPO3, SIGLA) ROW#
	FROM TABELA2 T2, TABELA1 T1 
        WHERE T2.COD = T1.COD
	ORDER BY ORDER BY T2.CAMPO1, T2.CAMPO2, T2.CAMPO3, T1.SIGLA ) T
	WHERE ROW# <= 13 )
	
	START WITH ROW#=1
	CONNECT BY PRIOR T2.CAMPO1=T2.CAMPO1
	AND PRIOR T2.CAMPO2=T2.CAMPO2
        AND PRIOR T2.CAMPO2=T2.CAMPO3
	AND PRIOR ROW# = ROW# -1 
	GROUP BY  T2.CAMPO1, T2.CAMPO2, T2.CAMPO3
	ORDER BY T2.CAMPO1 DESC, T2.CAMPO2 DESC, T2.CAMPO3 ASC

Observações:

  • O 13 é para limitar o número de valores concatenados a no máximo 13, e caso hajam mais valores do que 13, é acrescentado “…”. Se quiserem podem retirar/modificar essa parte
  • SIGLA é o campo cujos valores serão concatenados

É isso pessoal, qualquer dúvida postem aí!

#13