Help needed with crosstab design

(7 posts) (2 voices)
  1. fellamans, Member

    Hello everyone, I am creating a report in mydbr. I have put together the data. When using crosstab on date row it makes all data for the specific date run horizontally but i need the rows run vertically. So date to run horizontally but all else vertically. How could this be done?

    How i want it

    12.01.2021 11.01.2021 ...
    Registrations 12 11
    Downloads 14 12

    Thank you in advance.

  2. myDBR Team, Key Master

    how does your data query look like? How many separate datas do you have (Registrations, Downloads)?

    myDBR Team

  3. fellamans, Member

    So currently i have 19 separate datas. I will put an image attachment to how it looks currently and how i want it to look. The query itself is quite long.

  4. myDBR Team, Key Master

    What you are doing is not actually a crosstab, but you are comparing rows to eachother. Use instead.

    select '';
    select thedate, unique_visitors, registration_started...
    from yourdata;

    myDBR Team

  5. fellamans, Member

    This is absolutely what I needed. Thank you very much for your quick reply. Very happy with your support!


  6. fellamans, Member

    As I kept building the report, I noticed only gives me 2 columns. Is there a possibility to get dynamic nr. of columns? I am using indatestart and indateend to choose the period for the data and would like to have the full range of dates as columns in the report.

  7. myDBR Team, Key Master

    Does your query return more than 2 rows? The will generate a column for each row you have in the result set.

    If your data is missing dates and you want to get columns for each date between the given dates (indatestart and indateend), you will need to generate empty data for the result set (use temp table for the dates and LEFT JOIN that with the original data).

    You can open a support ticket with SQL export of the final report.
    myDBR Team


You must log in to post.