transpose column headers to rows in postgresql
I had a similar situation. I wrapped my query in a with statement and then did a bunch of UNION ALL
s for each row. In my situation, if I had multiple records, the ncm_id would be different, so I went ahead and added that to my column list in my result set. This may not be the best way to do this, but it worked for my use case.
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;
Crosstab only does the reverse of what you need, but this should help you:
First create the unnest()
function that is included in 8.4, see here for instructions.
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"
I can verify that this works in 8.4, but because I don't have 8.1, I can't promise it will work the same.
I achieved your goal by using hstore
's functionalities :
SELECT (x).key, (x).value
FROM
( SELECT EACH(hstore(t)) as x
FROM t
) q;
May you have multiple rows in your "to-be-exploded" view or table (referred here as t
), you might need to insert an additionnal identifier in the intermediate table q
, for example:
SELECT id, (x).key, (x).value
FROM
( SELECT id, EACH(hstore(t)) as x
FROM t
) q;
Reference: hstore
documentation
I was looking to do something similar to this to more easily process table information from a bash script. Turns out to be very easy to tell psql to display table columns as rows:
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.
This of course wont work in SQL, it only modifies how psql
happens to format output.