-
Notifications
You must be signed in to change notification settings - Fork 1.2k
/
sp_pivot_for.sql
124 lines (112 loc) · 5.51 KB
/
sp_pivot_for.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
/**********************************************************************************************
Purpose: Transpose row values into columns
Notes: Emulates result of PIVOT aggregate() FOR syntax
Syntax:
SELECT [ $(select_cols) , ] $(generated PIVOT columns)
FROM $(table_name)
PIVOT ( $(agg_func) ( $(metric_col) )
FOR $(pivot_src) IN ( SELECT DISTINCT $(pivot_src) FROM $(table_name) ) ) pvt
[ WHERE $(where_clause) ]
[ GROUP BY $(select_cols) ] ;'
Parameters:
select_cols : Text list of columns to be added to the SELECT clause
table_name : Schema qualified name of table to be queried
pivot_src : Name of the column whose value will be pivoted
agg_func : Name of the aggregate function to apply
metric_col : Name of the column to be aggregated
where_clause: Text of predicate criteria to be added to the WHERE clause
result_set : Name of cursor used for output
History:
2019-05-25 - joeharris76 - Created
2019-11-04 - joeharris76 - Revised for publication
**********************************************************************************************/
-- DROP PROCEDURE sp_pivot(VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,REFCURSOR);
CREATE OR REPLACE PROCEDURE sp_pivot_for (
select_cols IN VARCHAR(512)
, table_name IN VARCHAR(256)
, pivot_src IN VARCHAR(128)
, agg_func IN VARCHAR(32)
, metric_col IN VARCHAR(128)
, where_clause IN VARCHAR(512)
, result_set INOUT REFCURSOR )
AS $$
DECLARE
pivot_col RECORD;
final_sql VARCHAR(MAX) := '';
pivot_sql VARCHAR(MAX) := '';
val_cnt INTEGER := 0;
col_cnt SMALLINT := 0;
BEGIN
-- Check the number of values being pivoted. Placeholders used for NULL and zero length values.
pivot_sql := 'SELECT COUNT(DISTINCT CASE WHEN '||quote_ident(pivot_src)||' IS NULL THEN ''<NULL>'''||
' WHEN '||quote_ident(pivot_src)||' = '''' THEN ''<EMPTY>'''||
' ELSE '||quote_ident(pivot_src)||' END ) AS val_cnt '||
'FROM '||table_name||' ;';
EXECUTE pivot_sql INTO val_cnt;
IF val_cnt > 256 THEN -- Limit columns to prevent excessive width
RAISE EXCEPTION 'Too many values to pivot. Found % but limit is 256.', val_cnt;
END IF;
IF select_cols <> '' THEN
final_sql := 'SELECT '||select_cols||', ';
ELSE
final_sql := 'SELECT ';
END IF;
-- Query to get the row values being pivoted
pivot_sql := 'SELECT DISTINCT CASE WHEN '||quote_ident(pivot_src)||' IS NULL THEN ''<NULL>'''||
' WHEN '||quote_ident(pivot_src)||' = '''' THEN ''<EMPTY>'''||
' ELSE '||quote_ident(pivot_src)||' END AS col_n '||
'FROM '||table_name||CASE WHEN where_clause <> '' THEN ' WHERE '||where_clause ELSE '' END||' ORDER BY 1;';
-- Iterate over the row values
FOR pivot_col IN EXECUTE pivot_sql
LOOP
col_cnt := col_cnt + 1;
IF col_cnt > 1 THEN
final_sql := final_sql||', ';
END IF;
-- Add the CASE statement for each column to the SQL
IF pivot_col.col_n = '<NULL>' THEN
final_sql := final_sql||agg_func||'(CASE WHEN '||quote_ident(pivot_src)||' IS NULL THEN '||quote_ident(metric_col)
||' ELSE NULL END) AS '||quote_ident(pivot_col.col_n);
ELSIF pivot_col.col_n = '<EMPTY>' THEN
final_sql := final_sql||agg_func||'(CASE WHEN '||quote_ident(pivot_src)||' = '''' THEN '||quote_ident(metric_col)
||' ELSE NULL END) AS '||quote_ident(pivot_col.col_n);
ELSE
final_sql := final_sql||agg_func||'(CASE WHEN '||quote_ident(pivot_src)||' = '||quote_literal(pivot_col.col_n)
||' THEN '||quote_ident(metric_col)||' ELSE NULL END) AS '||quote_ident(pivot_col.col_n);
END IF;
END LOOP;
final_sql := final_sql||' FROM '||table_name;
IF where_clause <> '' THEN
final_sql := final_sql||' WHERE '||where_clause||' ';
END IF;
IF select_cols <> '' THEN
final_sql := final_sql||' GROUP BY '||select_cols||' ;';
ELSE
final_sql := final_sql||' ;';
END IF;
-- Open the cursor and execute the SQL
OPEN result_set FOR EXECUTE final_sql;
END;
$$ LANGUAGE plpgsql;
/* Usage Example:
BEGIN;
DROP TABLE IF EXISTS tmp_state_metrics;
CREATE TEMP TABLE tmp_state_metrics ( country VARCHAR, state VARCHAR, entries INTEGER);
INSERT INTO tmp_state_metrics VALUES
('USA', NULL, 15177), ('USA', NULL, 1)
, ('USA', 'AK', 7001), ('USA', 'AK', 1)
, ('USA', 'AL', 20225), ('USA', 'AL', 1)
, ('USA', 'AR', 22772), ('USA', 'AR', 1)
, ('USA', 'AZ', 4577), ('USA', 'AZ', 1)
, ('USA', 'CA', 17877), ('USA', 'CA', 1)
, ('USA', 'CO', 19503), ('USA', 'CO', 1);
CALL sp_pivot_for ('country','tmp_state_metrics','state','SUM','entries','','pivot_result');
FETCH ALL FROM pivot_result; CLOSE pivot_result;
-- country | <null> | ak | al | ar | az | ca | co
-- USA | 15178 | 7002 | 20226 | 22773 | 4578 | 17878 | 19504
CALL sp_pivot_for ('country','tmp_state_metrics','state','SUM','entries','state IS NOT NULL','pivot_result');
FETCH ALL FROM pivot_result;
-- country | ak | al | ar | az | ca | co
-- USA | 7002 | 20226 | 22773 | 4578 | 17878 | 19504
END;
*/