moster67 Expert Licensed User Longtime User Dec 19, 2022 #1 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 2016Client 2017Supplier 2016Supplier 2017Mr. GreenUSD 500USD 300USD 200USD 400 from a table (let us say an Invoice table) as follows (of course it could be an csv-file): Mr GreenClient, year 2016USD 500Mr GreenClient, year 2017USD 300 Mr GreenSupplier, year 2016USD 200Mr Green,Supplier, year 2017USD 400 It's late and I am stuck but perhaps someone can give me a helping hand. Probably involves grouping.. Thanks. Last edited: Dec 19, 2022
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 2016Client 2017Supplier 2016Supplier 2017Mr. GreenUSD 500USD 300USD 200USD 400 from a table (let us say an Invoice table) as follows (of course it could be an csv-file): Mr GreenClient, year 2016USD 500Mr GreenClient, year 2017USD 300 Mr GreenSupplier, year 2016USD 200Mr Green,Supplier, year 2017USD 400 It's late and I am stuck but perhaps someone can give me a helping hand. Probably involves grouping.. Thanks.
JohnC Expert Licensed User Longtime User Dec 20, 2022 #2 It would be cool is ChatGPT could provide the answer to this! Upvote 0
E emexes Expert Licensed User Longtime User Dec 20, 2022 #3 JohnC said: It would be cool if ChatGPT could provide the answer to this! Click to expand... 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 Upvote 0
JohnC said: It would be cool if ChatGPT could provide the answer to this! Click to expand... 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 Member Licensed User Longtime User Dec 20, 2022 #4 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' Upvote 0
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 Expert Licensed User Longtime User Dec 20, 2022 #5 LGS said: Try this: Click to expand... Thank you. Very much appreciated. Upvote 0
J JohnJ Member Licensed User Longtime User Dec 20, 2022 #6 moster67 said: Thank you. Very much appreciated. Click to expand... You might want to google "listagg" Upvote 0
moster67 said: Thank you. Very much appreciated. Click to expand... You might want to google "listagg"
J JohnJ Member Licensed User Longtime User Dec 21, 2022 #7 JohnJ said: You might want to google "listagg" Click to expand... 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 Upvote 0
JohnJ said: You might want to google "listagg" Click to expand... 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
