This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT
and would require only one function that turns the string into a CASE
statement.
SELECT substr(
MIN (
CASE WHEN Blank=1 THEN '1 Blank'
WHEN Error=1 THEN '2 Error'
WHEN InProgress=1 THEN '3 InProgress'
WHEN Completed=1 THEN '4 Completed'
END
)
,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;
I assume the data looks something like this:
drop table m_object;
create table m_object as
(select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);
drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);
Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.
set serveroutput on format wrapped
DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';
Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
vBuilt Varchar2(500);
vWord Varchar2(100);
vChar Char(1);
vWordCount Number(1) := 1;
Begin
For vLoop In 1..Length(pOriginal) Loop
vChar := substr(pOriginal,vLoop,1);
If (vChar = ',') Then
If (vBuilt IS NULL) Then
vBuilt := 'CASE WHEN ';
End If;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
vWord := '';
vWordCount := vWordCount + 1;
Else
vWord := vWord || vChar;
End If;
End Loop;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
vBuilt := vBuilt || ' END ';
Return vBuilt;
End;
BEGIN
vPassed := MakeCase(vPassed);
DBMS_Output.Put_Line(vPassed);
--Use vPassed here to build SQL statement.
END;
/
If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query
SELECT
CONCAT(id, '-', date),
MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
id,
date;
If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.
Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:
SELECT
CONCAT(id, '-', date),
GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table
GROUP BY
id,
date;
You can also change the list to be delimited by pipes instead of commas like this:
SELECT
CONCAT(id, '-', date),
GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table
GROUP BY
id,
date;
The default maximum length of a GROUP_CONCAT is 1024.
You need to change that max length in the session using this:
SET group_concat_max_len = 10240;
before you issue your query.
Give it a Try !!!
Best Answer
Assuming
2016-04-05 0:27:15
instead ofin the underlying table, the question would make more sense to me:2016-04-05 1:27:15
The logic would be to count events that happened up to and excluding the next bound. This fits the often overlooked function
width_bucket()
perfectly. To be precise, it requires the variant with arbitrary bounds (since there is no regular pattern in the OP's bounds) introduced with Postgres 9.5. Explanation straight from the manual:For regular buckets you can use another variant that's available in Postgres 9.1 as well.
Combine it with
crosstab()
re-using the same bounds as column names (the rest of the query works with Postgres 9.1):Result:
The second crosstab parameter (
'SELECT generate_series(0,3)'
) is a query string when executed returning one row for every target column. Every value not found on either side - not in the raw data or not generated by the 2nd parameter - is simply ignored.Basics for
crosstab()
:Replace NULL with 0
If you need
0
instead ofNULL
in the result, fix withCOALESCE()
, but that's merely a cosmetic problem:Result:
Adding totals
To add totals per
status
use the newGROUPING SETS
in Postgres 9.5+Result like above, plus:
Note that
total
includes all rows not excluded before aggregation, even if filtered bycrosstab()
.This is in reply to @VĂ©race's request in the comments rather than to the unclear question.