SELECT fund_type, unnest(array['fund1', 'fund2', 'fund3', 'fund4', 'fund5', 'fund6']) AS fund_name, 
                  unnest(array[fund1, fund2, fund3, fund4, fund5, fund6]) AS fund_value, sign_val
FROM fund_flag;

字符串

您可以使用以下查询来获取所需的输出。以下查询将帮助您说明所需的输出：

SELECT
  fund_type,
  'fund' || column_number AS fund_name,
  unnested_data.column_value AS fund_value,
  unnested_data.sign_val
FROM (
  SELECT
    fund_type,
    unnest(array[fund1, fund2, fund3, fund4, fund5, fund6]) AS column_value,
    generate_series(1, 6) AS column_number,
    sign_val
  FROM fund_flag
) AS unnested_data
ORDER BY fund_type, column_number;

字符串

要在PostgreSQL中取消透视表，可以使用UNION ALL运算符来合并多个SELECT语句，每个语句选择一个列，并添加一个新列来标识原始列名。例如：

SELECT fund_type, 'fund1' AS fund_name, fund1 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund2' AS fund_name, fund2 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund3' AS fund_name, fund3 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund4' AS fund_name, fund4 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund5' AS fund_name, fund5 AS fund_value, sign_val
FROM fund_flag
UNION ALL
SELECT fund_type, 'fund6' AS fund_name, fund6 AS fund_value, sign_val
FROM fund_flag
ORDER BY fund_type, fund_name;

字符串
也许你也可以参考这个问题：Equivalent to unpivot() in PostgreSQL

动态处理标签。您可以to_jsonb()整个记录，而无需硬编码标签列表，-仅保留您想要的标签，然后jsonb_each()列出它们。Demo:

select fund_type, fund_name, fund_value::bool, sign_val
from fund_flag,
jsonb_each(to_jsonb(fund_flag)-'{fund_type,sign_val}'::text[])_(fund_name,fund_value);

字符串
| 基金类型|基金名称|基金价值|符号_瓦尔|
| --|--|--|--|
| SGP|基金1|不|- -一种|
| SGP|基金2| F|- -一种|
| SGP|基金3| F|- -一种|
| SGP|基金4| F|- -一种|
| SGP|基金5|不|- -一种|
| SGP|基金6| F|- -一种|
| USD|基金1|不|+的|
| USD|基金2| F|+的|
| USD|基金3|不|+的|
| USD|基金4|不|+的|
| USD|基金5|不|+的|
| USD|基金6| F|+的|
| INR|基金1| F|- -一种|
| INR|基金2| F|- -一种|
| INR|基金3|不|- -一种|
| INR|基金4| F|- -一种|
| INR|基金5|不|- -一种|
| INR|基金6|不|- -一种|

在Oracle或SQL Server中，这可以通过UNPIVOT操作符来实现，但在Postgres中不可用。然而，您可以使用VALUES()和JOIN LATERAL来取消透视列：

select f.fund_type, t.*, f.sign_val
from fund_flag f
  cross join lateral (
     values 
       (f.fund1, 'fund1'),
       (f.fund2, 'fund2'),
       (f.fund3, 'fund3'),
       (f.fund4, 'fund4'),
       (f.fund5, 'fund5'),
       (f.fund6, 'fund6')
  ) as t(fund_name, fund_value)
order by fund_type;

字符串
Demo here