4
SELECT ID,NAME,VALUE1,VALUE2 FROM my_table where ID=1 ;

The query would give me output like 

ID|NAME|VALUE1|VALUE2
1|XYZ|123|325

But I want the output in the below format

ID    |1
NAME  |XYZ
VALUE1|123
VALUE2|325

My columns will be fixed and each time the result will be a single row, how can I modify the query to get the below result?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Swathi8038
  • 96
  • 2
  • 11
  • You can do this, but it's better practice to pivot the data in your client program. – Joel Coehoorn Jan 30 '18 at 05:54
  • check my implementation of [unpivot](https://github.com/sKwa/vertica/blob/master/UDx/PolymorphicUnpivot.cpp). Modify it to your needs. – sKwa Jan 30 '18 at 09:40
  • 1
    I don't believe there's any v-sql functionality like this out of the box without writing a union for everything. Like @sKwa suggested you could load a custom UDF and unpivot that way. Relevant discussion here:https://forum.vertica.com/discussion/219577/vertica-sql-data-transformation – colinwurtz Feb 06 '18 at 18:06
  • 2
    @colinwurtz, I actually already did it [here](https://forum.vertica.com/discussion/239423/unpivot-functionality). But who cares? – sKwa Feb 06 '18 at 19:11
  • @sKwa pretty slick, I may try and use this – colinwurtz Feb 06 '18 at 21:22
  • @colinwurtz feel free. I will improve it in near feature: support for all data types + performance issues. – sKwa Feb 06 '18 at 21:39
  • @sKwa thank u so much this is what exactly i wanted – Swathi8038 Feb 07 '18 at 05:40
  • @Swathi8038, keep updated, I will improve function, so far its just a concept/pilot. – sKwa Feb 07 '18 at 05:55

3 Answers3

3

Check this example, it do exactly what you need.

UPDATE on comment:

but what if i have multiple columns and rows how can i transpose

My UDF will do transpose/unpivot for each row, for example:

daniel=> select * from test limit 4;
 user_id | day_of_week | to_sample
---------+-------------+-----------
       1 |           1 |         1
       1 |           2 |         2
       2 |           1 |         1
       2 |           2 |         2
(4 rows)

daniel=> select unpivot(*) over () from test limit 8;
     KEY     | VALUE
-------------+-------
 user_id     | 1
 day_of_week | 1
 to_sample   | 1
 user_id     | 1
 day_of_week | 2
 to_sample   | 2
 user_id     | 2
 day_of_week | 1
(8 rows)
sKwa
  • 889
  • 5
  • 11
  • Thank u but what if i have multiple columns and rows how can i transpose – Swathi8038 Feb 14 '18 at 04:32
  • @Swathi8038: What do you mean? Can you provide an example? Or can you open a ticket on GitHub if some functionality is missing? My UDF will do `unpivot` for each row. – sKwa Feb 14 '18 at 04:54
  • @Swathi8038: open a new question, because functionality that you are asking its - `PIVOT` and not `UNPIVOT`. – sKwa Feb 14 '18 at 23:17
1

One approach which should be ANSI compliant would be to just use a series of unions:

SELECT 'ID' AS key, ID AS value FROM my_table WHERE ID=1
UNION ALL
SELECT 'NAME', NAME FROM my_table WHERE ID=1
UNION ALL
SELECT 'VALUE1', VALUE1 FROM my_table WHERE ID=1
UNION ALL
SELECT 'VALUE2', VALUE2 FROM my_table WHERE ID=1;

If you also need the exact ordering in your expected output, then we could add a computed ordering column to the union query, then subquery and order by that.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

this also should work

SELECT StringTokenizerDelim((ID || ',' || NAME || ',' || VALUE1 || ',' || VALUE2), ',') over (partition by null) FROM my_table where ID = 1
Igor Aldabaev
  • 11
  • 4