Convert SQL query to Pivot using dates (Oracle)

Question

everyone.

I am trying to convert this Oracle SQL Query to Pivot but I cannot figue it out how.

SELECT TO_CHAR (STARTTIME, 'YYYY/MM/DD')                AS DATETIME,
         COUNT (*)                                      AS QTY,
         DECODE (STATE,'P','PASS','F','FAIL',  STATE)   AS STATUS
    FROM R_TEST_RECORD
   WHERE SN IN (SELECT SN
                  FROM R_SN
                 WHERE SKUNO = '750-062572')
     AND MESSTATION = 'SI_V1'
     AND STARTTIME BETWEEN TO_DATE ('2020/06/02 01:00:49',
                                    'YYYY/MM/DD HH24:MI:SS')
                       AND TO_DATE ('2021/10/02 01:53:49',
                                    'YYYY/MM/DD  HH24:MI:SS')
GROUP BY TO_CHAR (STARTTIME, 'YYYY/MM/DD'), STATE
ORDER BY DATETIME;

Query gives me next table:

DATETIME	QTY	STATUS
2021/03/16	106	PASS
2021/03/17	731	PASS
2021/03/19	102	PASS
2021/04/05	102	PASS
2021/06/08	100	PASS
2021/06/15	99	PASS
2021/06/20	712	PASS
2021/06/22	149	PASS
2021/06/29	33	PASS
2021/06/30	6	PASS
2021/07/02	2	PASS
2021/07/04	2	PASS

What I would like to have instead is this:

STATUS	2021/03/16	2021/03/17	2021/03/19	2021/04/05
PASS	106	731	102	102

Also I would like it to be dynamic so I do not have to hardcode dates in the Pivot Statement.

What I have worked is next:

SELECT *
FROM (
    SELECT TO_CHAR (starttime, 'YYYY/MM/DD') starttime,
    STATE
    FROM r_test_record A
   WHERE     sn IN (SELECT sn
                      FROM r_sn
                     WHERE skuno = '750-062572')
         AND messtation = 'SI_V1'
         AND starttime BETWEEN TO_DATE ('2021/06/02 01:00:49',
                                        'YYYY/MM/DD HH24:MI:SS')
                           AND TO_DATE ('2021/10/02 01:53:49',
                                        'YYYY/MM/DD  HH24:MI:SS')
) 
PIVOT
(   
    COUNT(STATE)
    FOR STARTTIME IN (  
      SELECT DISTINCT TO_CHAR (starttime, 'YYYY/MM/DD') STARTTIME
   FROM r_test_record
  WHERE     sn IN (SELECT sn
                     FROM r_sn
                    WHERE skuno = '750-062572')
        AND messtation = 'SI_V1'
        AND starttime BETWEEN TO_DATE ('2021/06/02 01:00:49',
                                       'YYYY/MM/DD HH24:MI:SS')
                          AND TO_DATE ('2021/10/02 01:53:49',
                                       'YYYY/MM/DD  HH24:MI:SS')
    )
)

Thanks for your help.

What if the data has 100 different dates? Do you want a result with 100 columns? Who can read such a report? (This is not telling you how to solve the problem - it is just telling you that almost certainly the problem itself doesn't make sense.) — , Oct 04 '21 at 17:26
You are totally right but I will segregate dates by weeks so it will only show 7 days. I really appreciate your comment. — A Rifkin Guy, Oct 04 '21 at 17:29
If it will always be seven days (a week), then it's a lot more reasonable. The only problem will be to show the dates as column names; that can only be done with dynamic SQL, and is best handled by your reporting software (assuming you use one). Will that work for you? — , Oct 04 '21 at 17:47

score 0 · Answer 1 · answered Oct 04 '21 at 18:47

One option if you are going to limit it to seven columns would be to implicitly group by the WEEK_OF that way you could keep multiple rows. Something like this:

SELECT *
FROM (SELECT TRUNC(r.starttime, 'DAY') AS WEEK_OF, 
             TO_CHAR(r.starttime, 'D') AS DAY_OF_WEEK, 
             status
      FROM r_test_record r)
PIVOT (COUNT(*) FOR DAY_OF_WEEK IN (1 AS SUNDAY, 2 AS MONDAY, 3 AS TUESDAY,
                                               4 AS WEDNESDAY, 5 AS THURSDAY,
                                               6 AS FRIDAY, 7 AS SATURDAY))
ORDER BY WEEK_OF, STATUS;

You would lose the actual date in the column name, but you could run it for a full report. Or you could add a where clause to limit the dates.

Convert SQL query to Pivot using dates (Oracle)

1 Answers1