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.