SQL Query Syntax help
Hi Team,Can you please help me in writing the syntax for the below mentioned table.Input : ID Col A Col B Col C Col D Col E Col F 1111 aaaa bbbb cccc dddd eeee ffff 2222 bbbb cccc dddd eeee ffff aaaa 3333 cccc dddd eeee ffff aaaa bbbb 4444 dddd eeee ffff aaaa bbbb cccc 5555 eeee ffff aaaa bbbb cccc dddd 6666 ffff aaaa bbbb cccc dddd eeee 7777 cccc dddd eeee ffff aaaa bbbb 8888 dddd eeee ffff aaaa bbbb cccc 9999 ffff aaaa bbbb cccc dddd eeee Required Output : ID Col Name Col Value 1111 Col A aaaa 1111 Col B bbbb 1111 Col C cccc 1111 Col D dddd 1111 Col E eeee 1111 Col F ffff 2222 Col A bbbb 2222 Col B cccc 2222 Col C dddd 2222 Col D eeee 2222 Col E ffff 2222 Col F aaaa 3333 Col A cccc 3333 Col B dddd 3333 Col C eeee 3333 Col D ffff 3333 Col E aaaa 3333 Col F bbbb And so on for the 4444, 5555, 6666, 7777, 8888, 9999Thanks,Srikanth B
Hi,
Am Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:
[sniped HTML]
something like
select
id, 'Col A' as "Col Name", "Col A" as "Col Value"
from input
union all
select
id, 'Col B' as "Col Name", "Col B" as "Col Value"
from input
...
order by
1, 2;
should work.
--
Kind regards Jan
srikkanth wrote:
Can you please help me in writing the syntax for the below mentioned
This looks like an UNPIVOT operation.
Here's a generic method that does this without having to specify the
columns individually, with the help of json functions:
SELECT ID, key, value FROM
(SELECT ID, row_to_json(t.*) AS line FROM PivotTableName t) AS r
JOIN LATERAL json_each_text(r.line) on (key <> 'ID');
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
On Wed, 22 Jan 2020 at 11:00, srikkanth <srikkanth16081991@rediffmail.com>
wrote:
Can you please help me in writing the syntax for the below mentioned table.
Suggest looking at the crosstab function.
https://www.postgresql.org/docs/current/tablefunc.html
crosstab(text source_sql, text category_sql)
Produces a "pivot table" with the value columns specified by a second
query
Geoff
Hi Jan,Thanks for your input, it is working exactly what i want, but still i m facing issue because in my table there are different data types they are 10 to 15 columns are Boolean, one primary key, 140 columns are character.when i m applying the logic on this scenario i m getting the below error.ERROR: UNION types boolean and character cannot be matchedLINE 10: ..._ID","Critical_Decision" as col_Name, "Critical_... ^SQL state: 42804Character: 585After applying the logic i need to take this data into the table.can you please help me how to write the syntax in this case.Thanks,Srikanth BFrom: Jan Kohnert <nospam001-lists@jan-kohnert.de>Sent: Wed, 22 Jan 2020 17:11:56To: pgsql-general@lists.postgresql.orgSubject: Re: SQL Query Syntax helpHi,Am Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:[sniped HTML]something likeselect id, 'Col A' as "Col Name", "Col A" as "Col Value"from inputunion allselect id, 'Col B' as "Col Name", "Col B" as "Col Value"from input...order by 1, 2;should work.--Kind regards Jan
On Wednesday, January 22, 2020, srikkanth <srikkanth16081991@rediffmail.com>
wrote:
Hi Jan,
Thanks for your input, it is working exactly what i want, but still i m
facing issue because in my table there are different data types they are 10
to 15 columns are Boolean, one primary key, 140 columns are character.when i m applying the logic on this scenario i m getting the below error.
ERROR: UNION types boolean and character cannot be matched
LINE 10: ..._ID","Critical_Decision" as col_Name, "Critical_... ^
SQL state: 42804
Character: 585After applying the logic i need to take this data into the table.
can you please help me how to write the syntax in this case.
Cast everything to text?
https://www.postgresql.org/docs/12/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
David J.