Conversion of the data view

[gkvi...@gmail.com]

Hello

Follow, i have some data stored in a table

Is there an easy way to get the data into the desired form? The evaluation corresponds to a pivot table. My question is whether I can solve that relatively easy via a SQL query or SP. I hope someone has a tip how I can solve this.

Thanks

Gregor

[Karol Bieniaszewski]

It is simple if you know the numer of columns

 

As you can do this in this way, you can sum, min, max…

 

SELECT

T.DT

, MIN(CASE WHEN T.NUMBER=1 THEN T.V ELSE NULL END) AS "1"

, MIN(CASE WHEN T.NUMBER=2 THEN T.V ELSE NULL END) AS "2"

, MIN(CASE WHEN T.NUMBER=3 THEN T.V ELSE NULL END) AS "3"

FROM

TAB T

GROUP BY T.DT

 

Here is dbfiddle to test:

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=9e831e13a8848792276e0b09d356e971

 

regards,

Karol Bieniaszewski

 

Od: gkvi...@gmail.com
Wysłano: niedziela, 14 marca 2021 15:33
Temat: [firebird-support] Conversion of the data view

 

Hello

 

Follow, i have some data stored in a table

 

Is there an easy way to get the data into the desired form? The evaluation corresponds to a pivot table. My question is whether I can solve that relatively easy via a SQL query or SP. I hope someone has a tip how I can solve this.

 

Thanks

Gregor

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e76cc2f1-3116-4b71-8670-a52df90801a2n%40googlegroups.com.

 

[Dimitry Sibiryakov]

14.03.2021 15:33, gkvi...@gmail.com wrote:
> Is there an easy way to get the data into the desired form?

No on server, yes in an application.

--
WBR, SD.

[gkvi...@gmail.com]

Thanks for the tips, you have pointed me in the right direction.

Gregor

[Svein Erling Tysvær]

Normally, I would make pivot tables in Excel (or similar) rather than in queries. However, on rare occasions, a simple solution could be (suitable for your sample data, but normally sample data are simplifications of real data):

with tmp as
(select distinct dt from tab)
select t.DT, t1.Value as "1", t2.Value as "2", t3.Value as "3"
from tmp
left join tab t1 on tmp.dt = t1.dt and t.NUMBER = 1
left join tab t2 on tmp.dt = t2.dt and t.NUMBER = 2
left join tab t3 on tmp.dt = t3.dt and t.NUMBER = 3

Set

man. 15. mar. 2021 kl. 07:35 skrev gkvi...@gmail.com <gkvi...@gmail.com>:

Thanks for the tips, you have pointed me in the right direction.

Gregor

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/782c9e58-4d08-417d-84c0-59d8a5c91228n%40googlegroups.com.