Транспонировать заголовки столбцов в строки в postgresql

У меня есть вид, который выглядит так

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

Мне нужно переместить заголовок столбца в строки, и поэтому мне нужно, чтобы он выглядел как

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

Я могу сделать это, выбрав отдельные имена столбцов в качестве первого столбца и данные в качестве второго столбца, а затем сделать объединение одного и того же для всех столбцов.

Есть лучший способ сделать это? Я использую PosgreSQL 8.1, поэтому у меня нет опорных операторов для работы.

Спасибо за ваш ответ заранее.

Источник

7 ответов

Решение

Crosstab делает только то, что вам нужно, но это должно помочь вам:

Сначала создайте unnest() функция, которая включена в 8.4, смотрите здесь для инструкций.

Тогда вы можете сделать это (основываясь на этом посте):

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 документация

Источник

Я хотел сделать что-то похожее на это, чтобы легче обрабатывать информацию таблицы из скрипта bash. Оказывается, очень легко сказать psql отображать столбцы таблицы в виде строк:

psql mydbname -x -A -F= -c "select * from blah where id=123"
  • -x это то, что поворачивает вывод.
  • -A удаляет лишние пробелы
  • -F= заменяет | с = между именем столбца и значением.

Это, конечно, не будет работать в SQL, это только изменяет, как psql бывает с форматом вывода.

Источник

У меня была похожая ситуация. Я завернул свой запрос в оператор с, а затем сделал кучу UNION ALLs для каждого ряда. В моей ситуации, если бы у меня было несколько записей, 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

Вот что получилось примерно так: ht tps:https://stackru.com/images/6743377933e36bc097457cf812e6a7ad928a71dc.png

Кстати, это были данные о дорожно-транспортных происшествиях в Нью-Йорке.

Источник

Читая эти ответы, я чувствую, что использование WITH и UNION было бы намного проще; Никаких дополнительных функций или прочего:

      WITH tmp AS (SELECT val1, val2, val3 FROM view_name) 
   SELECT val1 FROM tmp UNION
   SELECT val2 FROM tmp UNION
   SELECT val3 FROM tmp;
Источник

Вот способ использования hstore, очень похожий на ответ Mickaël Le Baillif , но немного более лаконичный, позволяющий динамически выбирать подмножество столбцов и показывающий, как вы можете включать более обычные «жестко закодированные» столбцы, такие как идентификатор , которые обычно требуются в более реалистичных случаях.

      -- 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 есть обратная сторона: он по пути приводит значения к тексту, поэтому вам может потребоваться привести значения обратно к другому типу, в зависимости от вашего варианта использования.

Источник
Другие вопросы по тегам