Thread: SQL Query Syntax help

SQL Query Syntax help

From

"srikkanth"

Date:

22 January 2020, 14:00:45

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

Re: SQL Query Syntax help

From

"Daniel Verite"

Date:

22 January 2020, 14:58:33

    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

Re: SQL Query Syntax help

From

"Daniel Verite"

Date:

22 January 2020, 14:58:33

    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

Re: SQL Query Syntax help

From

Geoff Winkless

Date:

22 January 2020, 15:06:01

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

Re: SQL Query Syntax help

From

Geoff Winkless

Date:

22 January 2020, 15:06:01

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.

Geoff