Crosstab or Pivot in Native Vertica SQL

Moderator: NorbertKrupa

Post Reply
rthacker02
Newbie
Newbie
Posts: 4
Joined: Thu Nov 06, 2014 9:17 pm

Crosstab or Pivot in Native Vertica SQL

Post by rthacker02 » Fri Nov 07, 2014 3:43 pm

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,

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Crosstab or Pivot in Native Vertica SQL

Post by JimKnicely » Fri Nov 07, 2014 4:33 pm

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

Image

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

rthacker02
Newbie
Newbie
Posts: 4
Joined: Thu Nov 06, 2014 9:17 pm

Re: Crosstab or Pivot in Native Vertica SQL

Post by rthacker02 » Fri Nov 07, 2014 4:47 pm

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.

rthacker02
Newbie
Newbie
Posts: 4
Joined: Thu Nov 06, 2014 9:17 pm

Re: Crosstab or Pivot in Native Vertica SQL

Post by rthacker02 » Fri Nov 07, 2014 4:49 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Crosstab or Pivot in Native Vertica SQL

Post by JimKnicely » Fri Nov 07, 2014 6:06 pm

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

Image

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

rthacker02
Newbie
Newbie
Posts: 4
Joined: Thu Nov 06, 2014 9:17 pm

Re: Crosstab or Pivot in Native Vertica SQL

Post by rthacker02 » Fri Nov 07, 2014 6:35 pm

This worked nicely. Thanks.

Post Reply

Return to “Vertica "How to..."”