How do I PIVOT in Vertica

Comments

• DaveT Vertica Employee Employee

  May 2017

  If you just want to create column values using the actual values in the DESCRIPTION column you can write the SQL with DECODE or CASE and have one column for each distinct value of DESCRIPTION. If ID is unique you don't need any aggregate functions. If ID is not unique you don't have to use SUM; you can use MIN or MAX or anything that works with a string. It doesn't really matter what aggregate is used, the purpose in this case is just to bring the like ID values into a single row.

  Of course, if you have conflicting information in different rows (such as both 1|Fulltime|YES and 1|Fulltime|NO) then then whether MIN or MAX does matter but which one is really correct?

  0
• Shayon_Sanyal

  May 2017

  Let me know if this is what you're looking for:

  SELECT ID,
  CASE WHEN EMPLOYED = 'YES' THEN MIN(DECODE(DESCRIPTION, 'Fulltime', 'Yes', 'No')) ELSE 'No' END AS "Fulltime",
  CASE WHEN EMPLOYED = 'YES' THEN MIN(DECODE(DESCRIPTION, 'Contract', 'Yes', 'No')) ELSE 'No' END AS "Contract",
  CASE WHEN EMPLOYED = 'YES' THEN MIN(DECODE(DESCRIPTION, 'Seasonal', 'Yes', 'No')) ELSE 'No' END AS "Seasonal",
  CASE WHEN EMPLOYED = 'YES' THEN MIN(DECODE(DESCRIPTION, 'Terminated', 'Yes', 'No')) ELSE 'No' END AS "Terminated",
  CASE WHEN EMPLOYED = 'YES' THEN MIN(DECODE(DESCRIPTION, 'Suspended', 'Yes', 'No')) ELSE 'No' END AS "Suspended"
  FROM Pivot_TEST GROUP BY ID, EMPLOYED order by ID;

  0
• Slaagh

  June 2017

  Thank you very much for this, but I think I am over complicating this. Here is a smaller table that I just want to PIVOT.

  create table test2
  (
  master_country varchar(25)
  );

  INSERT INTO test2 values ('French Polynesia');
  INSERT INTO test2 values ('Colombia');
  INSERT INTO test2 values ('Brazil');
  INSERT INTO test2 values ('Mauritius');
  INSERT INTO test2 values ('Faroe Islands');

  select * from test2;

  0
• Jim_Knicely - Select Field - Administrator

  June 2017

  Similar to the suggestion by Shayon_Sanyal...

  dbadmin=> select * from test2;

  master_country

  French Polynesia
  Colombia
  Brazil
  Mauritius
  Faroe Islands
  (5 rows)

  dbadmin=> select
  dbadmin-> max(decode(rn, 1, master_country, null)) || ', ' ||
  dbadmin-> max(decode(rn, 2, master_country, null)) || ', ' ||
  dbadmin-> max(decode(rn, 3, master_country, null)) || ', ' ||
  dbadmin-> max(decode(rn, 4, master_country, null)) || ', ' ||
  dbadmin-> max(decode(rn, 5, master_country, null)) master_country
  dbadmin-> from (select master_country, row_number() over () rn from test2) foo;

  master_country

  French Polynesia, Colombia, Brazil, Mauritius, Faroe Islands
  (1 row)

  0
• joergschaber Vertica Customer ✭

  September 2021

  Nice! What do you do, when the number of rows to pivot is not known? Is there something generic?

  0
• marcothesane - Select Field - Administrator

  September 2021

  We will soon publish a few use cases for Stored Procedures, which will soon be part of a Vertica 11 subversion.

  I actually have a pivot function that would do that for you in that collection.

  But it's early days just now, and won't work in the current version.

  0
• marcothesane - Select Field - Administrator

  September 2021

  The principle is this:
  With this table sensors:

  id|msr|val
  41|rpm|1000
  41|tmp|   25
  41|ps_i| 26.32
  

  You need this query:

  SELECT
    id
  , MAX(CASE msr WHEN 'rpm' THEN val END) AS val_rpm
  , MAX(CASE msr WHEN 'tmp' THEN val END) AS val_tmp
  , MAX(CASE msr WHEN 'ps_i' THEN val END) AS val_ps_i
  FROM sensors
  GROUP BY
    id
  ;
  

  You find that out by previously firing a:

  SELECT DISTINCT msr FROM sensors;
  

  .. and then generating as many MAX(CASE msr WHEN '???' THEN val END) AS val_???, as you get distinct values - and incorporate that into the final query.

  0
• Hibiki Vertica Employee Employee

  September 2021

  I have developed the sample UDx to transform the long-form data to the wide-form data. Please try to use it if you can.

  https://github.com/h-serizawa/vertica-extension-functions/tree/master/long2wide

  0
• marcothesane - Select Field - Administrator

  November 2021

  Now that Vertica 11.0.1 is out, and with that, support for forward cursors in Stored procedures - with the same table sensors as above, try this:

  Create the test table:

    DROP TABLE IF EXISTS sensors;
    CREATE TABLE sensors (id,msr,val) AS
              SELECT 41,'rpm' ,1000
    UNION ALL SELECT 41,'tmp' ,  25
    UNION ALL SELECT 41,'ps_i',  26.32
    UNION ALL SELECT 42,'rpm' ,1200
    UNION ALL SELECT 42,'tmp' ,  26
    UNION ALL SELECT 42,'ps_i',  21.32
    ;
  

  Then, with a stored procedure, you can pivot the table. Into a target (temporary) table that you can then select from:

    CALL public.pivot(
      'id'        -- list of identifers -> GROUP BY
    , 'msr'       -- key column
    , 'val'       -- value column
    , 'sensors'  -- in table name
    , 'horizontal'-- out, pivoted, temp table name
    );
    -- out NOTICE 2005:  
    -- out     *** Running this Query *** 
    -- out CREATE LOCAL TEMPORARY TABLE horizontal ON COMMIT PRESERVE ROWS AS
    -- out SELECT
    -- out   id
    -- out , MAX(CASE msr WHEN 'ps_i' THEN val END) AS val_ps_i
    -- out , MAX(CASE msr WHEN 'rpm' THEN val END) AS val_rpm
    -- out , MAX(CASE msr WHEN 'tmp' THEN val END) AS val_tmp
    -- out FROM sensors
    -- out GROUP BY
    -- out   id
    -- out  pivot 
    -- out -------
    -- out      0
  
    SELECT * FROM horizontal;
    -- out  id | val_ps_i | val_rpm | val_tmp 
    -- out ----+----------+---------+---------
    -- out  41 |    26.32 | 1000.00 |   25.00
    -- out  42 |    21.32 | 1200.00 |   26.00
  

  The stored procedure is in prototype mode, and actually outputs the query it has generated, and it is going to fire.

  And just for playing purposes: Here's the full source code for the stored procedure:

    DROP PROCEDURE IF EXISTS public.pivot(
      idlist VARCHAR
    , keyname VARCHAR
    , valname VARCHAR
    ,tbname VARCHAR
    ,pvtbname VARCHAR
    ) ;
  
    CREATE PROCEDURE public.pivot (
      IN idlist   VARCHAR -- list of identifers -> GROUP BY
    , IN keyname  VARCHAR -- key column
    , IN valname  VARCHAR -- value column
    , IN tbname   VARCHAR -- in table name
    , IN pvtbname VARCHAR -- out, pivoted, temp table name
    ) LANGUAGE PLpgSQL SECURITY INVOKER AS $$
    DECLARE
      i      INTEGER := 0;
      sq     VARCHAR;
      c      VARCHAR;
      r      VARCHAR;
    BEGIN
      i := 1;
      WHILE SPLIT_PART(idlist,',',i) <> '' LOOP
        IF i = 1 THEN
          sq:= CHR(10)||'CREATE LOCAL TEMPORARY TABLE '||pvtbname||' ON COMMIT PRESERVE ROWS AS'||
              CHR(10)||'SELECT'||CHR(10)||'  '|| SPLIT_PART(idlist,',',i);
        ELSE
          sq:= sq||CHR(10)||', '|| SPLIT_PART(idlist,',',i);
        END IF;
        i := i + 1;
      END LOOP ;
      FOR c IN EXECUTE
      'SELECT DISTINCT '||keyname||' FROM '||tbname||' ORDER BY 1' LOOP
        sq := sq||CHR(10)||', MAX(CASE '||keyname||' WHEN '||QUOTE_LITERAL(c)||' THEN '||valname||' END) AS '||valname||'_'||c;
      END LOOP;
      i := 1;
      sq := sq ||CHR(10)||'FROM '||tbname;
      LOOP
        EXIT WHEN  SPLIT_PART(idlist,',',i) = '';
        IF i = 1 THEN
          sq:= sq||CHR(10)||'GROUP BY'||CHR(10)||'  '|| SPLIT_PART(idlist,',',i);
        ELSE
          sq:= sq||CHR(10)||', '|| SPLIT_PART(idlist,',',i);
        END IF;
        i := i + 1;
      END LOOP ;
      -- vv test output ...
      RAISE NOTICE '
        *** Running this Query *** %',sq;
      -- ^^ test output ...
      EXECUTE sq;
    END;
    $$;
  
  

  Happy playing ....

  0