Question

我有这样的观点。

          value1count     value2count value3count
          ----------------------------------------
             25              35          55

I need to transpose the column header into rows and so I need it to look like

          Values              Count
         -----------------------------
           value1count         25
           value2count         35
           value3count         55

I can do this by selecting individual column names as first column and data as second column and then do a union of the same for all columns.

Is there a better way to do this? I am using PosgreSQL 8.1 and so don t have pivot operators to work with.

感谢您事先作出答复。

Answer 1

Crosstab only does the reverse of what you need, but this should help you:

首先创设了载于8.4中的<代码>unnest()功能,见。

Then you can do this (based on 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项,我可以允诺这样做。

Answer 2

I achieved your goal by using hstore s functionalities :

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

请允许我在您的“待开发”观点或表格(此处称为t)中添加一个补充识别符号,例如:

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

参考:rel=“noreferer”>。

Answer 3

我期待着采取类似的做法,以便更方便地从印本上处理表格信息。很容易将纸浆显示成行栏:

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

The -x is what pivots the output.
The -A removes extra spacing.
The -F= replaces | with = between the column name and the value.

当然,这在LEG中赢得了工作,它只是对<条码>psql的改动。格式产出。

Answer 4

我的情况类似。我在发言中总结了我的问候,然后做了一个跳板的<代码>。 UNION ALLs for each row. 我的情况是,如果我有多份记录,那门单是不同的,那么我就走了前面,并补充说,在我的分组中,我的结果是确定的。这也许不是这样做的最佳方式,而是为我的工作。

 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;

Answer 5

我不能理解......但是,我这样做的方式也是选择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

That s what created something like so:

This was NYC motor vehicle crash data btw.

Answer 6

这里是使用 h的一种方式,如。 Le Baillif swer,但稍微简明扼要,使你能够积极选择一栏子,并表明你如何能够包括更经常的“硬编码”栏,如一个身份识别栏,在更现实的情况下往往需要。

-- 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$ ;

值得注意的是,“仓储”办法有 down:它把价值观放在正文中,因此,根据你的使用情况,你可能需要将价值重新引入另一种类型。

Answer 7

在阅读这些答案时,我感到,利用这些答案和看法会更简单;没有额外的职能或不足:

WITH tmp AS (SELECT val1, val2, val3 FROM view_name) 
   SELECT val1 FROM tmp UNION
   SELECT val2 FROM tmp UNION
   SELECT val3 FROM tmp;

友情链接