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,
Crosstab or Pivot in Native Vertica SQL
Moderator: NorbertKrupa
-
- Newbie
- Posts: 4
- Joined: Thu Nov 06, 2014 9:17 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Crosstab or Pivot in Native Vertica SQL
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)
Jim Knicely

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 4
- Joined: Thu Nov 06, 2014 9:17 pm
Re: Crosstab or Pivot in Native Vertica SQL
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.
-
- Newbie
- Posts: 4
- Joined: Thu Nov 06, 2014 9:17 pm
Re: Crosstab or Pivot in Native Vertica SQL
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.
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.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Crosstab or Pivot in Native Vertica SQL
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)
Jim Knicely

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 4
- Joined: Thu Nov 06, 2014 9:17 pm
Re: Crosstab or Pivot in Native Vertica SQL
This worked nicely. Thanks.