| Subject | Re: [firebird-support] Re: Full Cross Tab but unknown column headers |
|---|---|
| Author | W O |
| Post date | 2013-12-27T20:56:06Z |
Well, it is not complicated at all, I was waiting for somebody writing a better technique but the days go on and that not happen then I show you a link to a page where the technique is described, with two examples. The page is in Spanish but you can use some translator (Google has one). And of course, if you have any question, just tell me, here or in the page:
One drawback of the technique is that you need to know in advance the exact number of columns or at least the maximum number of columns.
Greetings.
Walter.
On Fri, Dec 27, 2013 at 7:28 AM, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>Hi allHi Bhavbhuti!
>
>Merry Christmas everyone!
>
>I have had to revisit this issue. I have a simplified table
>Color, Amount
>Red, 123
>Green, 234
>Blue, 345
>
>Previously, I have been advised of CTEs and CASE before wherein I was able to get the result but for it I had to assume that such and such Colors names existed.
>But the thing is that the Color value is not under control and is not a fixed list, so one user could have a
>Light Green, 456
>and another user could define the same as
>Pale Green, 567
>I want the colors to become the header in the output and the Amount underneath,something like
>
>Red, Green, Blue, Light Green, Pale Green
>123, 234, 345, 456, 567
>
>Please advise
SQL - at least the way Firebird implements it - is basically lousy at turning rows into columns, this is work more suitable for something like Excel (PivotTable), FastReport or some other tools/components. I think it is doable in Firebird using EXECUTE STATEMENT inside EXECUTE BLOCK, but it sounds more complicated than the alternatives mentioned above. I think one of the reasons for this being complicated to do in SQL, is that you normally need to know the number and type of columns in advance.
Sorry,
Set