| Subject | RE: [firebird-support] Vertical data to Horizontal data |
|---|---|
| Author | Gerardo Arana |
| Post date | 2009-08-11T20:41:57Z |
Hi,
Thank you for yours reply
Gerardo.
--- El lun 10-ago-09, Elkins Villalona <evillalona@...> escribió:
De: Elkins Villalona <evillalona@...>
Asunto: RE: [firebird-support] Vertical data to Horizontal data
A: firebird-support@yahoogroups.com
Fecha: lunes, 10 agosto, 2009, 10:16 am
Using the same example table ( table1) proposed by Hans You may try this
query
select code,
min(case when alias = '1001' Then vaue end) val1,
min(case when alias = '1002' Then vaue end) val2,
min(case when alias = '1003' Then vaue end) val3,
min(case when alias = 'nn' Then vaue end) val4
from table1
group by code
De: firebird-support@ yahoogroups. com
[mailto:firebird-support@ yahoogroups. com] En nombre de Hans
Enviado el: domingo, 09 de agosto de 2009 01:49 a.m.
Para: firebird-support@ yahoogroups. com
Asunto: Re: [firebird-support] Vertical data to Horizontal data
Try this:
CREATE TABLE TABLE1 (
CODE VARCHAR( 8 )
, ALIAS VARCHAR( 8 )
, VAUE VARCHAR( 8 )
)
populate with your example values, then create procedure
CREATE PROCEDURE TABLE1_HORZ
RETURNS ( CODE VARCHAR( 8 )
, ALIAS1 VARCHAR( 8 )
, ALIAS2 VARCHAR( 8 )
, ALIAS3 VARCHAR( 8 )
, ALIAS4 VARCHAR( 8 )
, ALIAS5 VARCHAR( 8 )
, ALIAS6 VARCHAR( 8 )
, ALIAS7 VARCHAR( 8 )
, ALIAS8 VARCHAR( 8 )
, ALIAS9 VARCHAR( 8 ) )
AS
DECLARE VARIABLE TOTALCODES INTEGER = 9;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE ALIAS VARCHAR(8);
BEGIN
ALIAS1 = '';
ALIAS2 = '';
ALIAS3 = '';
ALIAS4 = '';
ALIAS5 = '';
ALIAS6 = '';
ALIAS7 = '';
ALIAS8 = '';
ALIAS9 = '';
I = 1;
CODE = 'CODE';
FOR SELECT DISTINCT ALIAS FROM TABLE1
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = :ALIAS;
ELSE
IF (I = 2) THEN ALIAS2 = :ALIAS;
ELSE
IF (I = 3) THEN ALIAS3 = :ALIAS;
ELSE
IF (I = 4) THEN ALIAS4 = :ALIAS;
ELSE
IF (I = 5) THEN ALIAS5 = :ALIAS;
ELSE
IF (I = 6) THEN ALIAS6 = :ALIAS;
ELSE
IF (I = 7) THEN ALIAS7 = :ALIAS;
ELSE
IF (I = 8) THEN ALIAS8 = :ALIAS;
ELSE
IF (I = 9) THEN ALIAS9 = :ALIAS;
I = I + 1;
END
SUSPEND;
I = 1;
CODE = '--------';
FOR SELECT DISTINCT ALIAS FROM TABLE1
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = '--------';
ELSE
IF (I = 2) THEN ALIAS2 = '--------';
ELSE
IF (I = 3) THEN ALIAS3 = '--------';
ELSE
IF (I = 4) THEN ALIAS4 = '--------';
ELSE
IF (I = 5) THEN ALIAS5 = '--------';
ELSE
IF (I = 6) THEN ALIAS6 = '--------';
ELSE
IF (I = 7) THEN ALIAS7 = '--------';
ELSE
IF (I = 8) THEN ALIAS8 = '--------';
ELSE
IF (I = 9) THEN ALIAS9 = '--------';
I = I + 1;
END
SUSPEND;
FOR SELECT DISTINCT CODE FROM TABLE1
ORDER BY CODE
INTO :CODE
DO
BEGIN
I = 1;
FOR SELECT VAUE FROM TABLE1
WHERE CODE = :CODE
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = :ALIAS;
ELSE
IF (I = 2) THEN ALIAS2 = :ALIAS;
ELSE
IF (I = 3) THEN ALIAS3 = :ALIAS;
ELSE
IF (I = 4) THEN ALIAS4 = :ALIAS;
ELSE
IF (I = 5) THEN ALIAS5 = :ALIAS;
ELSE
IF (I = 6) THEN ALIAS6 = :ALIAS;
ELSE
IF (I = 7) THEN ALIAS7 = :ALIAS;
ELSE
IF (I = 8) THEN ALIAS8 = :ALIAS;
ELSE
IF (I = 9) THEN ALIAS9 = :ALIAS;
I = I + 1;
END
SUSPEND;
END
END
then run
SELECT * FROM TABLE1_HORZ
and the results will be indentical to what you seem to want :)
Thank you for yours reply
Gerardo.
--- El lun 10-ago-09, Elkins Villalona <evillalona@...> escribió:
De: Elkins Villalona <evillalona@...>
Asunto: RE: [firebird-support] Vertical data to Horizontal data
A: firebird-support@yahoogroups.com
Fecha: lunes, 10 agosto, 2009, 10:16 am
Using the same example table ( table1) proposed by Hans You may try this
query
select code,
min(case when alias = '1001' Then vaue end) val1,
min(case when alias = '1002' Then vaue end) val2,
min(case when alias = '1003' Then vaue end) val3,
min(case when alias = 'nn' Then vaue end) val4
from table1
group by code
De: firebird-support@ yahoogroups. com
[mailto:firebird-support@ yahoogroups. com] En nombre de Hans
Enviado el: domingo, 09 de agosto de 2009 01:49 a.m.
Para: firebird-support@ yahoogroups. com
Asunto: Re: [firebird-support] Vertical data to Horizontal data
Try this:
CREATE TABLE TABLE1 (
CODE VARCHAR( 8 )
, ALIAS VARCHAR( 8 )
, VAUE VARCHAR( 8 )
)
populate with your example values, then create procedure
CREATE PROCEDURE TABLE1_HORZ
RETURNS ( CODE VARCHAR( 8 )
, ALIAS1 VARCHAR( 8 )
, ALIAS2 VARCHAR( 8 )
, ALIAS3 VARCHAR( 8 )
, ALIAS4 VARCHAR( 8 )
, ALIAS5 VARCHAR( 8 )
, ALIAS6 VARCHAR( 8 )
, ALIAS7 VARCHAR( 8 )
, ALIAS8 VARCHAR( 8 )
, ALIAS9 VARCHAR( 8 ) )
AS
DECLARE VARIABLE TOTALCODES INTEGER = 9;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE ALIAS VARCHAR(8);
BEGIN
ALIAS1 = '';
ALIAS2 = '';
ALIAS3 = '';
ALIAS4 = '';
ALIAS5 = '';
ALIAS6 = '';
ALIAS7 = '';
ALIAS8 = '';
ALIAS9 = '';
I = 1;
CODE = 'CODE';
FOR SELECT DISTINCT ALIAS FROM TABLE1
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = :ALIAS;
ELSE
IF (I = 2) THEN ALIAS2 = :ALIAS;
ELSE
IF (I = 3) THEN ALIAS3 = :ALIAS;
ELSE
IF (I = 4) THEN ALIAS4 = :ALIAS;
ELSE
IF (I = 5) THEN ALIAS5 = :ALIAS;
ELSE
IF (I = 6) THEN ALIAS6 = :ALIAS;
ELSE
IF (I = 7) THEN ALIAS7 = :ALIAS;
ELSE
IF (I = 8) THEN ALIAS8 = :ALIAS;
ELSE
IF (I = 9) THEN ALIAS9 = :ALIAS;
I = I + 1;
END
SUSPEND;
I = 1;
CODE = '--------';
FOR SELECT DISTINCT ALIAS FROM TABLE1
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = '--------';
ELSE
IF (I = 2) THEN ALIAS2 = '--------';
ELSE
IF (I = 3) THEN ALIAS3 = '--------';
ELSE
IF (I = 4) THEN ALIAS4 = '--------';
ELSE
IF (I = 5) THEN ALIAS5 = '--------';
ELSE
IF (I = 6) THEN ALIAS6 = '--------';
ELSE
IF (I = 7) THEN ALIAS7 = '--------';
ELSE
IF (I = 8) THEN ALIAS8 = '--------';
ELSE
IF (I = 9) THEN ALIAS9 = '--------';
I = I + 1;
END
SUSPEND;
FOR SELECT DISTINCT CODE FROM TABLE1
ORDER BY CODE
INTO :CODE
DO
BEGIN
I = 1;
FOR SELECT VAUE FROM TABLE1
WHERE CODE = :CODE
ORDER BY ALIAS
INTO
:ALIAS
DO
BEGIN
IF (I = 1) THEN ALIAS1 = :ALIAS;
ELSE
IF (I = 2) THEN ALIAS2 = :ALIAS;
ELSE
IF (I = 3) THEN ALIAS3 = :ALIAS;
ELSE
IF (I = 4) THEN ALIAS4 = :ALIAS;
ELSE
IF (I = 5) THEN ALIAS5 = :ALIAS;
ELSE
IF (I = 6) THEN ALIAS6 = :ALIAS;
ELSE
IF (I = 7) THEN ALIAS7 = :ALIAS;
ELSE
IF (I = 8) THEN ALIAS8 = :ALIAS;
ELSE
IF (I = 9) THEN ALIAS9 = :ALIAS;
I = I + 1;
END
SUSPEND;
END
END
then run
SELECT * FROM TABLE1_HORZ
and the results will be indentical to what you seem to want :)
----- Original Message -----
From: "Gerardo" <gasuel@yahoo. com <mailto:gasuel% 40yahoo.com> >
To: <firebird-support@ yahoogroups. com
<mailto:firebird- support%40yahoog roups.com> >
Sent: Saturday, August 08, 2009 4:57 PM
Subject: [firebird-support] Vertical data to Horizontal data
> Good evening,
>
> I have a table with data on vertical
>
> Table1.
>
> Code alias vaue
> ------------ --------- --
> 0001 1001 234
> 0002 1001 4566
> 0003 1001 1256
> 0004 1001 1256
> 0001 1002 256
> 0002 1002 148
> 0003 1002 126
> 0004 1002 4856
> 0001 1003 458
> 0002 1003 4587
> 0003 1003 256
> 0004 1003 8956
> 0001 nn xx
> 0002 nn xx
> 0003 nn xx
> 0004 nn xx
>
> I would like a Query, let me do this
>
>
> code 1001 1002 1003 nn
> ------------ --------- --------- -------
> 0001 234 256 458 xx
> 0002 4566 148 4587 xx
> 0003 1256 126 256 xx
> 0004 1256 4856 8956 xx
>
>
> thanks,
>
> Gerardo.
>
>
>
> ------------ --------- --------- ------
>
> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
>
> Visit http://www.firebird sql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoeni x.com
>
> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
____________________________________________________________________________________
¡Obtén la mejor experiencia en la web!
Descarga gratis el nuevo Internet Explorer 8.
http://downloads.yahoo.com/ieak8/?l=e1
[Non-text portions of this message have been removed]