Subject RE: [firebird-support] CrossTab Select
Author Edward Mendez
Post date 2014-08-19T21:13:58Z

I am I including the original question in case someone has better answer

 

I am assuming that if you have 2 produtos for the same time period that you will want both of them displayed in the same row like so “CEBOLA,AJO”.

 

SELECT HORA, COALESCE( LIST(SEG),0) seg, COALESCE( LIST(TER),0) ter, COALESCE( LIST(QUA),0) qua, COALESCE( LIST(QUI),0) qui, COALESCE( LIST(SEX),0) sex, COALESCE( LIST(SAB),0) sab, COALESCE( LIST(DOM),0)dom from (

Select HORA, produto, MAX(SEG) seg, MAX(TER) ter, MAX(QUA) qua, MAX(QUI) qui, MAX(SEX) sex, MAX(SAB) sab, MAX(DOM) dom from (

SELECT HORA, PRODUTO,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 0 THEN PRODUTO ELSE NULL END) SEG,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 1 THEN PRODUTO ELSE NULL END) TER,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 2 THEN PRODUTO ELSE NULL END) QUA,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 3 THEN PRODUTO ELSE NULL END) QUI,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 4 THEN PRODUTO ELSE NULL END) SEX,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB)  WHEN 5 THEN PRODUTO ELSE NULL END) SAB,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 6 THEN PRODUTO ELSE NULL END) DOM

FROM TABLEAB

WHERE TABLEAB.DATAB BETWEEN '06/22/2014'

      AND '06/28/2014' ) T1

GROUP BY PRODUTO, HORA ) T2

GROUP BY  HORA

 

Here is the data I am using for this example.

 

TABLEAB_ID    DATAB     HORA           PRODUTO

          0    6/22/2014 9:00:00 AM    CEBOLA

          0    6/23/2014 9:00:00 AM    CEBOLA

          0    6/24/2014 9:00:00 AM    CEBOLA

          0    6/25/2014 9:00:00 AM    CEBOLA

          0    6/26/2014 9:00:00 AM    CEBOLA

          0    6/27/2014 9:00:00 AM    CEBOLA

          0    6/28/2014 9:00:00 AM    CEBOLA

          1    6/22/2014 9:10:00 AM    CEBOLA

          2    6/25/2014 9:00:00 AM    AJO

 

Below is the output

 

HORA|SEG|TER|QUA|QUI|SEX|SAB|DOM

09:00:00|CEBOLA|CEBOLA|CEBOLA|CEBOLA,AJO|CEBOLA|CEBOLA|CEBOLA

09:10:00|CEBOLA|0|0|0|0|0|0

 

If you want different produtos on different rows then run the query below;

 

SELECT HORA, COALESCE( LIST(SEG),0) seg, COALESCE( LIST(TER),0) ter, COALESCE( LIST(QUA),0) qua, COALESCE( LIST(QUI),0) qui, COALESCE( LIST(SEX),0) sex, COALESCE( LIST(SAB),0) sab, COALESCE( LIST(DOM),0) dom from (

SELECT HORA, PRODUTO,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 0 THEN PRODUTO ELSE NULL END) SEG,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 1 THEN PRODUTO ELSE NULL END) TER,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 2 THEN PRODUTO ELSE NULL END) QUA,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 3 THEN PRODUTO ELSE NULL END) QUI,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 4 THEN PRODUTO ELSE NULL END) SEX,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB)  WHEN 5 THEN PRODUTO ELSE NULL END) SAB,

       ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 6 THEN PRODUTO ELSE NULL END) DOM

FROM TABLEAB

WHERE TABLEAB.DATAB BETWEEN '06/22/2014'

      AND '06/28/2014' ) T1

GROUP BY PRODUTO, HORA

 

With the following output

 

HORA|SEG|TER|QUA|QUI|SEX|SAB|DOM

09:00:00|0|0|0|AJO|0|0|0

09:00:00|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA

09:10:00|CEBOLA|0|0|0|0|0|0

 

I hope this helps.

 

Thanks,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, June 25, 2014 6:29 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] CrossTab Select

 

 

Partners'm racking my brain on a SQL code, does anyone could give a force

I made the following SP

CREATE PROCEDURE PRODS(
PAC INTEGER)
RETURNS(
HOR TYPE OF HORA,
SEG TYPE OF DESCRICAO,
TER TYPE OF DESCRICAO,
QUA TYPE OF DESCRICAO,
QUI TYPE OF DESCRICAO,
SEX TYPE OF DESCRICAO,
SAB TYPE OF DESCRICAO,
DOM TYPE OF DESCRICAO)
AS
BEGIN
FOR SELECT HORA, (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END) from TABELAB
WHERE TABELAB.DATABBETWEEN '23.06.2014' AND '28.06.2014'
GROUP BY HORA, PRODUTO, DATAB


INTO HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND;

END

Correct Return

#Código

1

2

3

HORA  SEG                   TER                    QUA                   QUI                  SEX                  SAB                  DOM

09:00 CEBOLA          CEBOLA         CEBOLA        CEBOLA      CEBOLA       CEBOLA      0

09:10 CEBOLA          0                         0                        0                      0                        0



Incorrect Return

#Código

1

2

3

4

5

6

7

HORA  SEG              TER                QUA                QUI                 SEX                SAB                 DOM

09:00 0                     0                      0                     0                    0                    CEBOLA  

09:00 0                     0                      0                     0                    CEBOLA        0                    0

09:00 0                     0                      0                     CEBOLA       0                    0                    0

09:00 0                     0                      CEBOLA       0                    0                    0                    0

09:00 0                     CEBOLA       0                     0                    0                    0                    0

09:00 CEBOLA    0                       0                     0                    0                    0                    0