Need to display the query result in colums

  • dubem1-878067

    Ten Centuries

    Points: 1036

    Query a table gives this result

    ContractId    ContractStartDate   ContactEndDate  ContractValue   ContractManager

    1                      2019-11-01                 2020-06-30           45000                Stephen Smith

    2                      2019-12-01                2020-07-30           75000                Carol Kenin

    3                      2020-01-15                2020-08-30           25000               John Gnan

    4                      2020-02-01               2020-09-30           40000               Jacob Joklin

    The report should display like this (each row is a colum)

    Contract Id               |         1                  |                2           |                3             |                 4

    Contract Start Date | 2019-11-01       |    2019-12-01     |    2020-01-15      |    2020-02-01

    Contact End Date    | 2020-06-30     |    2020-07-30   |    2020-08-30    |   2020-09-30

    Contract Value         |      45000          |        75000          |         25000         |        40000

    Contract Manager  | Stephen Smith |  Carol Kenin       |  John Gnan        |  Jacob Joklin

    The first column is hardcoded then the next columns represent each rows of the query result

    IS this something that can be done?

    thanks

    MArtin

  • pietlinden

    SSC Guru

    Points: 62735

    You'll get tested answers if you provide some consumable data... How about...

    SELECT ID, StartDate, EndDate, ContractValue, Manager
    FROM
    (VALUES (1,'2019-11-01','2020-06-30',45000,'Stephen Smith'),
    (2,'2019-12-01','2020-07-30',75000,'Carol Kenin'),
    (3,'2020-01-15','2020-08-30',25000,'John Gnan'),
    (4,'2020-02-01','2020-09-30',40000,'Jacob Joklin')) z(ID, StartDate, EndDate, ContractValue, Manager);

    Works if you add a matrix to the canvas and create a Column Group on ContractID, and then add the other columns and put then inside the group.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply