I'm a new to SQL so, can you please help me in figuring out how to get a key-value pair from a SQL for example : Table A
A(PK) B(INT) C D
------ ------- ------- --------
1 X Y Z
2 A B C
I want the output in the form
A = 1
B = X
C = Y
D = Z
i tried
SELECT A,B,C,D from tableA ;
But it only prints in the form 1 X Y Z So, can you please help me ?
Generally getting the column names along with the row values and formatting the result is a thing the SQL client provides for you. pgAdmin isn't really suited for this, a programming language would be better.
For example, here's how you'd do it using Ruby and the ruby-pg gem.
#!/usr/bin/env ruby
require 'pg'
conn = PG.connect( dbname: 'test' )
conn.exec( "SELECT * FROM tableA" ) do |result|
result.each do |row|
row.each do |column,value|
puts "#{column} = #{value}"
end
end
end
You can do a union of the queries picking one column at a time
SELECT 'A', A from tableA WHERE ID = @ID
UNION
SELECT 'B', B from tableA WHERE ID = @ID
UNION
SELECT 'C', C from tableA WHERE ID = @ID
UNION
SELECT 'D', D from tableA WHERE ID = @ID
you will probably need to cast all the columns to a common type (varchar) for this to work.
Another option is to create a function that returns the result as desired:
create type crosstab_type as (row_num bigint, column_name text, value text);
create or replace function kv()
returns setof crosstab_type
as
$$
declare
l_rec record;
l_row bigint := 1;
begin
for l_rec in select * from ani
loop
return next (l_row, 'a', l_rec.a::text)::crosstab_type;
return next (l_row, 'b', l_rec.b::text)::crosstab_type;
return next (l_row, 'c', l_rec.c::text)::crosstab_type;
return next (l_row, 'd', l_rec.d::text)::crosstab_type;
l_row := l_row + 1;
end loop;
end;
$$
language plpgsql;
select *
from kv();
returns:
row_num | column_name | value
--------+-------------+------
1 | a | 1
1 | b | X
1 | c | Y
1 | d | Z
2 | a | 2
2 | b | A
2 | c | B
2 | d | C
Online example: http://rextester.com/RCKB51862