Android Question Help with SQL - query

[moster67]

Don't know if it is possible but if it is, how can I using SQL get a query result as in this layout:

Person	Client 2016	Client 2017	Supplier 2016	Supplier 2017
Mr. Green	USD 500	USD 300	USD 200	USD 400

from a table (let us say an Invoice table) as follows (of course it could be an csv-file):

Mr Green	Client, year 2016	USD 500
Mr Green	Client, year 2017	USD 300
Mr Green	Supplier, year 2016	USD 200
Mr Green,	Supplier, year 2017	USD 400

It's late and I am stuck but perhaps someone can give me a helping hand.

Probably involves grouping..

Thanks.

 

[JohnC]

It would be cool is ChatGPT could provide the answer to this!

 

[emexes]

It would be cool if ChatGPT could provide the answer to this!

Are you doubting the hype? Are you a blasphemer or what? Next thing we know, you'll be saying the Hunter Laptop isn't Russian Disinformation.

https://meta.stackoverflow.com/questions/421831/temporary-policy-chatgpt-is-banned

 

[LGS]

Try this:

select distinct i1.person, i2.amount Client2016, i3.amount Client2017,i4.amount Supplier2016, i5.amount Supplier2017
from invoice i1
full join invoice i2 on i2.person = i1.person and i2.year = 'Client, year 2016'
full join invoice i3 on i3.person = i1.person and i3.year = 'Client, year 2017'
full join invoice i4 on i4.person = i1.person and i4.year = 'Supplier, year 2016'
full join invoice i5 on replace(i5.person,',','') = i1.person and i5.year = 'Supplier, year 2017'
where i1.person = 'Mr Green'

 

[moster67]

Try this:

Thank you. Very much appreciated.

 

[JohnJ]

Thank you. Very much appreciated.

You might want to google "listagg"

 

[JohnJ]

You might want to google "listagg"

Here is a link to an article explaining this. I know this is available in DB2 and Oracle, not sure about other databases.

New Solution for Combining Multiple Row Values into a Single Row with SQL - MC Press Online

SQL’s LISTAGG function makes combining row values easier.

www.mcpressonline.com