Is there any way where I can do ALIAS reference other column value?

Question

I am trying to figure out how to do ALIAS which referring the value of other column.

I am not sure it is possible, but does it acquire some tricks to make it done?

How can I perform query something like code below?

SELECT chd_value AS **chd_key value**
FROM table1 where chd_id = 1;

where the chd_key will be replaced with the value of "chd_key" column in the table.

So I will get output similar like

.

Answer 1

The key word is "pivoting" or "cross tabulation".

For a limited, known number of distinct values in chd_key the fastest technique is crosstab(). Like:

SELECT *
FROM   crosstab(
   'SELECT chd_id, chd_key, chd_value
    FROM   tbl
    where  chd_id = 1
    ORDER  BY 1'  -- ORDER BY redundant in this special case
  , $$VALUES ('food'), ('color'), ('gadget')$$
   ) AS ct (chd_id int, food text, color text, gadget text);

Detailed instructions:

• PostgreSQL Crosstab Query

But that's not fully dynamic, which is hard to accomplish with SQL. See:

• Dynamic alternative to pivot with CASE and GROUP BY

---

One way to simply get "key":"value" like you later commented:

SELECT format('"%s":"%s"', chd_key, chd_value) AS chd_pair
FROM tbl WHERE chd_id = 1;

Answer 2

This will not give you two rows like you show in your image, but if you just want the key/value pair in a single column, do a concatenation on your key/value pair:

SELECT (chd_key || ':' || chd_value) AS chd_pair
FROM table1 WHERE chd_id = 1;

To add double quotes in a quick and dirty fashion as requested:

SELECT ('"' || chd_key || '":"' || chd_value || '"') AS chd_pair
FROM table1 WHERE chd_id = 1;

It's not pretty, but it's easy and it works.

Answer 3

Based on the comments the author intends to use PHP, PDO can be used to obtain the desired output. For example, assuming $pdo is a PDO instance:

 $pdo->query('SELECT chd_key, chd_value FROM table1 where chd_id = 1')
     ->fetchAll(PDO::FETCH_KEY_PAIR)

The above will result in an associate array with the expected output. The first column used as the key and the second as the value:

[
  'food' => 'chiken'
]

https://www.php.net/manual/en/book.pdo.php

https://www.php.net/manual/en/pdostatement.fetchall.php