Home » SQL & PL/SQL » SQL & PL/SQL » ROWS to COLS
ROWS to COLS [message #192328] Mon, 11 September 2006 19:37 Go to next message
rjbuelvas
Messages: 1
Registered: July 2006
Location: BOGOTA
Junior Member
Hi, Rene.

I'm not speak English but I need that You help me, please.

I have the following two tables:

TABLE #1 TAB_HARDWARE
=========================
CREATE TABLE "TAB_HARDWARE"
( "CODIGO" NUMBER,
"TIPO" VARCHAR2(30),
"MARCA" VARCHAR2(30),
CONSTRAINT "TAB_HARDWARE_PK" PRIMARY KEY ("CODIGO") ENABLE
)
/


and your content is


CODIGO TIPO MARCA
1 PC Hewlet Packard
2 PC Dell
3 Server IBM
4 Storage IBM


TABLE #2 TAB_ATRIBUTOS_HARDWARE
=========================================
CREATE TABLE "TAB_ATRIBUTOS_HARDWARE"
( "TAB_HARDW_CODIGO" NUMBER,
"ATRIBUTO" VARCHAR2(240),
"VALOR_ATRIBUTO" VARCHAR2(240),
CONSTRAINT "TAB_ATRIBUTOS_HARDWARE_FK" FOREIGN KEY ("TAB_HARDW_CODIGO")
REFERENCES "TAB_HARDWARE" ("CODIGO") ENABLE
)
/

and your content is

TAB_HARDW_CODIGO ATRIBUTO VALOR_ATRIBUTO
1 PROCESADOR INTEL P4
1 RAM 512 MB
1 DISCO DURO 80 GB
3 PROCESADOR INTEL XEON 3.2
3 RAM 4 GB
3 DISCO DURO 120 GB


I need a query that show the records this way:

CODIGO TIPO MARCA PROCESADOR RAM DISCO_DURO
1 PC Hewlet Packard INTEL P4 512 MB 80 GB
3 Server Dell INTEL XEON 3.2 4 GB 120 GB
.
.
.


I'm using this query

SELECT h.codigo,
h.tipo,
h.marca,
decode(a.atributo, 'PROCESADOR', a.valor_atributo) procesador,
decode(a.atributo, 'RAM', a.valor_atributo) ram,
decode(a.atributo, 'DISCO DURO', a.valor_atributo) disco_duro
FROM tab_hardware h, tab_atributos_hardware a
WHERE h.codigo = a.tab_hardw_codigo

but the result is bad, becuase the item "hardware" is repeating for each rows in table details ("TAB_ATRIBUTOS_HARDWARE")


CODIGO TIPO MARCA PROCESADOR RAM DISCO_DURO
1 PC Hewlet Packard INTEL P4
1 PC Hewlet Packard 512 MB
1 PC Hewlet Packard 80 GB
3 Server IBM INTEL XEON 3.2
3 Server IBM 4 GB
3 Server IBM 120 GB




HELP ME, PLEASE.


Kind regards,


RAFAEL BUELVAS
_______________________________________________________________
Copyright 2006 Policia Nacional. Todos los derechos reservados.
URL: http://www.policia.gov.co
Mail: rafael.buelvas@policia.gov.co
Tel.: +57 1 3159596/3159377
Fax.: +57 1 3159593
Re: ROWS to COLS [message #192335 is a reply to message #192328] Mon, 11 September 2006 23:02 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Try this.

SELECT h.*,
(select valor_atributo from tab_atributos_hardware where atributo = 'PROCESADOR' and tab_hardw_codigo = h.codigo) PROCESADOR,
(select valor_atributo from tab_atributos_hardware where atributo = 'RAM' and tab_hardw_codigo = h.codigo) RAM,
(select valor_atributo from tab_atributos_hardware where atributo = 'DISCO DURO' and tab_hardw_codigo = h.codigo) "DISCO DURO"
FROM tab_hardware h
/


Liza
Re: ROWS to COLS [message #192339 is a reply to message #192328] Mon, 11 September 2006 23:41 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
You're nearly got it, the query just needs a max and group by.

select h.codigo,
h.tipo,
h.marca,
max(decode(a.atributo, 'PROCESADOR', a.valor_atributo)) procesador,
max(decode(a.atributo, 'RAM', a.valor_atributo)) ram,
max(decode(a.atributo, 'DISCO DURO', a.valor_atributo)) disco_duro
from tab_hardware h, tab_atributos_hardware a
where h.codigo = a.tab_hardw_codigo
group by h.codigo, h.tipo, h.marca;
Previous Topic: Question about getting the Top N or Bottom N of a dataset
Next Topic: wht would be my primary key
Goto Forum:
  


Current Time: Thu Dec 08 22:31:36 CST 2016

Total time taken to generate the page: 0.09564 seconds