交叉表只做你所需要的相反的事情，但是这应该会帮助你：
首先创建8.4中包含的unnest()函数，有关说明，请参见here。
然后可以这样做（基于this post）：

SELECT
   unnest(array['value1Count', 'value2Count', 'value3Count']) AS "Values",
   unnest(array[value1Count, value2Count, value3Count]) AS "Count"
FROM view_name
ORDER BY "Values"

我可以验证这在8.4中是否有效，但是因为我没有8.1，所以我不能保证它会同样有效。

我通过使用hstore的功能实现了您的目标：

SELECT (x).key, (x).value
FROM
  ( SELECT EACH(hstore(t)) as x
    FROM   t
  ) q;

如果“待分解”视图或表（此处称为t）中有多行，则可能需要在中间表q中插入一个附加标识符，例如：

SELECT id, (x).key, (x).value
FROM
  ( SELECT id, EACH(hstore(t)) as x
    FROM   t
  ) q;

参考：hstore documentation

我想做一些类似的事情来更容易地处理bash脚本中的表信息。结果证明告诉psql将表的列显示为行是非常容易的：

psql mydbname -x -A -F= -c "select * from blah where id=123"

• -x是旋转输出的对象。
• -A会移除额外的间距。
• -F=会在数据行名称和值之间，以=取代|。

这当然不会在SQL中工作，它只修改psql如何格式化输出。

我也遇到过类似的情况。我用with语句封装查询，然后对每一行执行一系列UNION ALL。在我的情况下，如果我有多条记录，ncm_id将是不同的，所以我继续将其添加到结果集中的列列表中。这可能不是最好的方法，但它对我的用例很有效。

WITH query_a AS (
     SELECT
       fin_item.item_number || ' - ' || fin_item.item_descrip1 fin_item,
       fin_ls.ls_number,
       ls_sort.sortby_employeeid,
       ls_sort.ncm_id,
       ls_sort.created_at,
       ls_sort.updated_at,
       ls_sort.sort_qty,
       ls_sort.initial_scan_time,
       ls_sort.ncm_scan_time,
       ls_sort.badge_scan_time,
       ls_sort.computer_name,
       ls_sort.number_of_ops,
       ls_sort.ncm_item_scan_time,
       sort_by.name sort_by,
       tblncm.instructions,
       tblncm.ncm_comments
     FROM public.item AS fin_item
       INNER JOIN public.ls AS fin_ls ON fin_item.item_id = fin_ls.ls_item_id
       INNER JOIN stone.ls_sort ON fin_ls.ls_id = ls_sort.ls_id
       INNER JOIN stone.vw_mssql_employees AS sort_by ON ls_sort.sortby_employeeid = sort_by.employeeid
       INNER JOIN stone.tblncm ON ls_sort.ncm_id = tblncm.ncm_id
       LEFT JOIN stone.equips AS mach_equips ON ls_sort.mach_equip_id = mach_equips.id
       LEFT JOIN stone.equips AS mold_equips ON ls_sort.mold_equip_id = mold_equips.id
     WHERE 1 = 1
           AND fin_ls.ls_number ILIKE 'S143027526190' || '%'
 )
   SELECT *
   FROM (
     (SELECT 'fin_item' my_column, fin_item::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ls_number' my_column, ls_number::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sortby_employeeid' my_column, sortby_employeeid::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_id' my_column, ncm_id::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'created_at' my_column, created_at::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'updated_at' my_column, updated_at::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sort_qty' my_column, sort_qty::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'initial_scan_time' my_column, initial_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_scan_time' my_column, ncm_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'badge_scan_time' my_column, badge_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'computer_name' my_column, computer_name::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'number_of_ops' my_column, number_of_ops::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_item_scan_time' my_column, ncm_item_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sort_by' my_column, sort_by::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'instructions' my_column, instructions::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_comments' my_column, ncm_comments::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
   ) as query_guy
 ORDER BY my_ncm;

我可能不明白...但我这样做的方式是也选择DISTINCT然后选择平均列值我需要的.像这样：

SELECT DISTINCT contributing_factor_vehicle_1, AVG(number_of_cyclist_injured) FROM "table1".motor_vehicle_collisions_crashes 
GROUP BY contributing_factor_vehicle_1
ORDER BY avg(number_of_cyclist_injured) desc

这就产生了这样的东西：

这是纽约市的机动车碰撞数据。

下面是一种使用hstore的方法，很像Mickaël Le Baillif's answer，但更简洁一些，它允许您动态地选择列的子集，并显示了如何包括更规则的“硬编码”列，如ID，这在更实际的情况下往往是必需的。

-- Example wide data, similar to the question, but with an extra "id" column
WITH example_wide_data("id", "value1count", "value2count", "value3count") AS (
  VALUES 
    (1, 4, 5, 6),
    (2, 8, 9, 10)
)

SELECT
  id,
  r.key AS value_name,
  r.value AS value
FROM
  example_wide_data w,
  each(hstore(w.*)) AS r(key, value)
WHERE
  -- Chooses the columns to transpose dynamically
  -- Difference cases will likely need a different condition
  r.key ~ '^value[0-9]+count$';

请注意，hstore方法有一个缺点：它会将值转换为文本，因此您可能需要将值转换回另一种类型，这取决于您的用例。