SQL Query Syntax help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Query Syntax help

srikkanth
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, 9999


Thanks,
Srikanth B
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Syntax help

Daniel Verite
        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


Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Syntax help

Geoff Winkless
In reply to this post by srikkanth
On Wed, 22 Jan 2020 at 11:00, srikkanth <[hidden email]> 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