Page 1 of 1
Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 3:43 pm
by rthacker02
I have a table as follows:
Market Division DMID DM_Name
Frontier WI 4486 John
Frontier IDMT 2462 David
Frontier NV 2462 David
Frontier NDSD 5326 Debbie
Frontier IA 5526 Mike
Frontier NE 5526 Mike
I need a query to produce this information in the following format:
Market Divisions DMID DM_Name
Frontier WI 4486 John
Frontier IDMT & NV 2462 David
Frontier NDSD 5326 Debbie
Frontier IA & NE 5526 Mike
Any ideas or recommendations would be appreciated. We don't have rights to administer Vertica so this has to be done in native SQL.
Thanks,
Re: Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 4:33 pm
by JimKnicely
Here is one way ...
Code: Select all
dbadmin=> SELECT * FROM t;
market | division | dmid | dm_name
----------+----------+------+---------
Frontier | NDSD | 5326 | Debbie
Frontier | NV | 2462 | David
Frontier | IA | 5526 | Mike
Frontier | IDMT | 2462 | David
Frontier | NE | 5526 | Mike
Frontier | WI | 4486 | John
(6 rows)
Code: Select all
dbadmin=> SELECT DISTINCT market,
dbadmin-> (CASE WHEN division IN ('IDMT', 'NV') THEN 'IDMT & NV' WHEN division IN ('IA', 'NE') THEN 'IA & NE' ELSE division END) division,
dbadmin-> dmid,
dbadmin-> dm_name
dbadmin-> FROM t;
market | division | dmid | dm_name
----------+-----------+------+---------
Frontier | WI | 4486 | John
Frontier | IA & NE | 5526 | Mike
Frontier | IDMT & NV | 2462 | David
Frontier | NDSD | 5326 | Debbie
(4 rows)
Re: Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 4:47 pm
by rthacker02
Thanks Jim but the sample data is just a subset of a much larger set that changes dynamically over time. I'll have to go with a more robust solution.
Re: Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 4:49 pm
by rthacker02
I found a snippet of code using NVL and DECODE and have modified it to the following:
SELECT DISTINCT vdm.Market, vdm.DMID, Trim(vdm.LastName)||', '||Trim(vdm.FirstName) as DM_Name, (
SELECT MAX(DECODE(rn, 1, division)) ||
NVL(MAX(DECODE(rn, 2, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 3, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 4, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 5, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 6, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 7, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 8, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 9, ' & ' || division)), '') ||
NVL(MAX(DECODE(rn, 10, ' & ' || division)), '') group_concate
FROM (SELECT row_number() over() rn, division FROM view_DivisionManagers ) foo ) as Divisions
FROM view_DivisionManagers vdm
GROUP BY vdm.DMID, vdm.Market, Trim(vdm.LastName)||', '||Trim(vdm.FirstName), Divisions
ORDER BY vdm.Market,DM_Name
This gets me close but each DMID has the same list of Divisions. I need to limit the subquery to the division associated with the DMID.
Re: Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 6:06 pm
by JimKnicely
Hmm. Maybe something like the following? It'll handle up to 10 distinct divisions... You can add more if needed.
Code: Select all
dbadmin=> select * from t;
market | division | dmid | dm_name
----------+----------+------+---------
Frontier | NDSD | 5326 | Debbie
Frontier | NV | 2462 | David
Frontier | NE | 5526 | Mike
Frontier | WI | 4486 | John
Frontier | IA | 5526 | Mike
Frontier | IDMT | 2462 | David
(6 rows)
Code: Select all
dbadmin=> select market, division_name as division, dmid, dm_name
dbadmin-> from (
dbadmin(> select market,
dbadmin(> division || nvl(' & ' || lag(division, 1) over (partition by dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 2) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 3) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 4) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 5) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 6) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 7) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 8) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 9) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 10) over (partition by market, dmid order by division), '' )
dbadmin(> as division_name,
dbadmin(> row_number() over (partition by market, dmid order by division) dmid_rn,
dbadmin(> count(dmid) over (partition by market, dmid) dmid_count,
dbadmin(> dmid,
dbadmin(> dm_name
dbadmin(> from t
dbadmin(> order by market, dmid, division ) foo
dbadmin-> where dmid_rn = dmid_count;
market | division | dmid | dm_name
----------+-----------+------+---------
Frontier | WI | 4486 | John
Frontier | NDSD | 5326 | Debbie
Frontier | NV & IDMT | 2462 | David
Frontier | NE & IA | 5526 | Mike
(4 rows)
Code: Select all
dbadmin=> insert into t values ('Frontier', 'XXX', 5526, 'Mike');
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> select market, division_name as division, dmid, dm_name
dbadmin-> from (
dbadmin(> select market,
dbadmin(> division || nvl(' & ' || lag(division, 1) over (partition by dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 2) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 3) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 4) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 5) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 6) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 7) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 8) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 9) over (partition by market, dmid order by division), '' )
dbadmin(> || nvl(' & ' || lag(division, 10) over (partition by market, dmid order by division), '' )
dbadmin(> as division_name,
dbadmin(> row_number() over (partition by market, dmid order by division) dmid_rn,
dbadmin(> count(dmid) over (partition by market, dmid) dmid_count,
dbadmin(> dmid,
dbadmin(> dm_name
dbadmin(> from t
dbadmin(> order by market, dmid, division ) foo
dbadmin-> where dmid_rn = dmid_count;
market | division | dmid | dm_name
----------+---------------+------+---------
Frontier | WI | 4486 | John
Frontier | NDSD | 5326 | Debbie
Frontier | NV & IDMT | 2462 | David
Frontier | XXX & NE & IA | 5526 | Mike
(4 rows)
Re: Crosstab or Pivot in Native Vertica SQL
Posted: Fri Nov 07, 2014 6:35 pm
by rthacker02
This worked nicely. Thanks.