Value as column name in sql

Asked Apr 08 '18 at 07:38

Active Apr 08 '18 at 07:54

Viewed 28 times

How to query SQL to convert key-value table to a bunch of column whose name is the key, and the value is the value?

I.E.

owner  | key    | value
----------------------------
   1   | AGE    | 15
   1   | HEIGHT | 155
   1   | WHAT   | Nothing
   1   | WHAT   | Something

to something like this:

owner  | AGE    | HEIGHT  | WHAT
----------------------------
   1   | 15     | 155     | Nothing;Something

I had a query which done it manually, is there a way to keep it generic? Thanks.

edited Apr 08 '18 at 07:54

Shadow

asked Apr 08 '18 at 07:38

aclowkay

You are unnecessarily wasting space in the second case. Why would you want this? Any specific reason / use case? – Varad Bhatnagar Apr 08 '18 at 07:43
@VaradBhatnagar I'm using the second table to index it to ElasticSearch. Also I changed it a bit, look at edited – aclowkay Apr 08 '18 at 07:46
There is something known as long form and wide form of data .Functions melt and cast are used in R . They are used to convert between the two tables that you have given. Try searching for something equivalent in SQL. – Varad Bhatnagar Apr 08 '18 at 07:51
see also: https://stackoverflow.com/q/19019453/1271037, https://stackoverflow.com/q/16568228/1271037, https://stackoverflow.com/q/3392956/1271037 and more. – dovid Apr 08 '18 at 07:51

0 Answers0