Thread: From CROSSTAB: One Column data with Two results columns

Hi,

I am working CROSSTAB functionality to generate the reports in the CSV format.

Now I have stage where I need to generate 2 result sets  for the same 1 column data.

I am sharing the example here.

I have attached a sample data file.

I am trying to know if I can generate this type of SQl ressult using CROSSTAB . I appreciate any hint or comments on this.

Thanks a lot,

Sarwar

Hello!

Am Sonntag, dem 14.04.2024 um 20:54 +0000 schrieb M Sarwar:
> Hi,
> I am working CROSSTAB functionality to generate the reports in the
> CSV format.
>  
> Now I have stage where I need to generate 2 result sets  for the same
> 1 column data.

Something like "CREATE VIEW"? The documentation has some useful
examples: https://www.postgresql.org/docs/current/sql-createview.html

"CREATE PROCEDURE" and "CREATE FUNCTION" are rarely covered by
tutorials, so I mention them. They can be used in a similar manner.


For CSV, I'm the wrong person. I'd code it in C/C++.

Regards,
Andreas Wagner

Hello Mr. Sarwar!

I'm not certain about first and last names in foreign countries, so my
apologies if it's your first name.

Am Montag, dem 15.04.2024 um 12:25 +0000 schrieb M Sarwar:
>
> Hello Andreas,
> Using a function, I am generating  CROSSTAB SQL and the number of
> columns will vary for each execution. With that, it is extremely
> difficult to use view on this task.


I'm still not sure whether I understand your question. You look for a
way to produce statements like

SELECT test1p1.data, test1p2.data, test1p3.data,
    f(test1p1.data), f(test1p2.data), f(test1p3.data)
FROM test1p1, test1p2, test1p3;

programmatically? ("f()" is a user-defined function deriving the left
values on the sample-picture to the right values.)

In case using multiple tables is new to you, have a look at the "JOIN"-
statement.

Regards,
Andreas Wagner

1. Row columns
2. Category column
3. Data / Results columns

1. Row columns
2. Category column
3. Data / Results columns

Hello Mr. Sarwar!

Am Montag, dem 15.04.2024 um 19:42 +0000 schrieb M Sarwar:
>
>  
> Hello Mr. Andreas,
>  ( Just correcting  the title / Sorry for my bad. )

Thanks for staying polite - even friendly - with me! For your benefit
in intercultural communication, I remark, in Germany, two ways of
writing down a name are common:
1. First name (= given name) last name (= surname or name of the
family), in my case, Andreas Wagner
2. Last name, first name. In my case: Wagner, Andreas.
I'm quite sure, in the first case, callign with the first name is
percieved ok while in the second case, calling with the last name is
preferred.

>
> You are using my name perfectly. 
> In the CROSSTAB clause, there are 3 sections.
>    1.
>       Row columns
>    2.
>       Category column
>    3.
>       Data / Results columns

This is why I'm happy to see you still patient; I never ran accross the
Crosstab() function before. I read the documentation a few minutes ago
and tried to play with Crosstab() but I seem to have unclear issues
with my computer. ("postgresql-server", openSUSE package doens't
install a service, no posgres-service running but the postgres user
exists. On the ARM-SBC, postgres is already installed in version 15 and
I seem to have issues with the syntax.)

> CROSSTAB provides option to fetch just 1 column while using CROSSTAB
> in normal circumstances.
> My question is 'is there anyway that I can fetch 2 columns?'.
> I am sharing the pictorial diagram where I need to fetch 2 columns
> data / results for the same category columns ( TEST1P1, TEST1P2,
> TEST1P3, TEST1P4 ).

I'm sorry, I can't answer your question.
Due to the issues with my openSUSE (Tumbleweed) install I consider
leaving. I have a terrible amount of hacking/remote sabotage, here and
this gives me the assumption, "they" want me to leave this community.

Regards,
Andreas Wagner,
presumably head of Wagner Group (or why the hell am I sabotaged by that
amount?! Even C-code doen't behave as I tell it to!)

PS
  Mails regarding my current schizo-boost please to me alone. The boost
will be over, soon, when I stop to care, why no postgres-service was
running after a regular "zypper install postgresql-server".
My apologies.