pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Query Syntax help
6+ messages / 5 participants
[nested] [flat]
* SQL Query Syntax help
@ 2020-01-22 11:00 srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 11:41 ` Re: SQL Query Syntax help Jan Kohnert <nospam001-lists@jan-kohnert.de>
2020-01-22 11:58 ` Re: SQL Query Syntax help Daniel Verite <daniel@manitou-mail.org>
2020-01-22 12:06 ` Re: SQL Query Syntax help Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 3 replies; 6+ messages in thread
From: srikkanth @ 2020-01-22 11:00 UTC (permalink / raw)
To: pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Query Syntax help
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
@ 2020-01-22 11:41 ` Jan Kohnert <nospam001-lists@jan-kohnert.de>
2020-01-22 12:07 ` Re: SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Jan Kohnert @ 2020-01-22 11:41 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Query Syntax help
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 11:41 ` Re: SQL Query Syntax help Jan Kohnert <nospam001-lists@jan-kohnert.de>
@ 2020-01-22 12:07 ` srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 15:34 ` Re: SQL Query Syntax help David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: srikkanth @ 2020-01-22 12:07 UTC (permalink / raw)
To: Jan Kohnert <nospam001-lists@jan-kohnert.de>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Query Syntax help
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 11:41 ` Re: SQL Query Syntax help Jan Kohnert <nospam001-lists@jan-kohnert.de>
2020-01-22 12:07 ` Re: SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
@ 2020-01-22 15:34 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2020-01-22 15:34 UTC (permalink / raw)
To: srikkanth <srikkanth16081991@rediffmail.com>; +Cc: Jan Kohnert <nospam001-lists@jan-kohnert.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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: 585
>
> After 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Query Syntax help
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
@ 2020-01-22 11:58 ` Daniel Verite <daniel@manitou-mail.org>
2 siblings, 0 replies; 6+ messages in thread
From: Daniel Verite @ 2020-01-22 11:58 UTC (permalink / raw)
To: srikkanth <srikkanth16081991@rediffmail.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Query Syntax help
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
@ 2020-01-22 12:06 ` Geoff Winkless <pgsqladmin@geoff.dj>
2 siblings, 0 replies; 6+ messages in thread
From: Geoff Winkless @ 2020-01-22 12:06 UTC (permalink / raw)
To: srikkanth <srikkanth16081991@rediffmail.com>; +Cc: pgsql-admin@postgresql.org, Postgres General <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-01-22 15:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-22 11:00 SQL Query Syntax help srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 11:41 ` Jan Kohnert <nospam001-lists@jan-kohnert.de>
2020-01-22 12:07 ` srikkanth <srikkanth16081991@rediffmail.com>
2020-01-22 15:34 ` David G. Johnston <david.g.johnston@gmail.com>
2020-01-22 11:58 ` Daniel Verite <daniel@manitou-mail.org>
2020-01-22 12:06 ` Geoff Winkless <pgsqladmin@geoff.dj>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox