Confirm

Expand all | Collapse all

SQL report - rows to columns

  • 1.  SQL report - rows to columns

    Posted 12-03-2020 17:13
    Hi all,

    I am trying to create a data source that will display the results in columns instead of rows. We have an enquiry subject that has 5 enquiry attributes and my data source will return the records but will have 5 rows for each enquiry. What I would like to do is have one row with 5 columns (one for each attribute) per enquiry.

    Does this make sense and can anyone help?

    thanks in advance

    Steven.

    ------------------------------
    Steven Broadfoot
    North Lanarkshire Council
    Bellshill
    ------------------------------


  • 2.  RE: SQL report - rows to columns

    Posted 12-04-2020 08:33

    Hi Steve,

    Hope the below is what you mean and will help, this is how I have done the attributes to give me in one row rather than 5:

    SELECT
    central_enquiry.enquiry_number,
    central_enquiry.enquiry_desc,
    central_enquiry.service_code,
    central_enquiry.subject_code,
    (SELECT
    parameter_value.param_value_name
    FROM
    enquiry_attribute,
    parameter_value
    WHERE
    enquiry_attribute.enquiry_number = central_enquiry.enquiry_number AND
    enquiry_attribute.param_type_code = parameter_value.param_type_code AND
    enquiry_attribute.param_value_code = parameter_value.param_value_code AND
    enquiry_attribute.param_type_code = 'CLLC') as claim_location,
    (SELECT
    parameter_value.param_value_name
    FROM
    enquiry_attribute,
    parameter_value
    WHERE
    enquiry_attribute.enquiry_number = central_enquiry.enquiry_number AND
    enquiry_attribute.param_type_code = parameter_value.param_type_code AND
    enquiry_attribute.param_value_code = parameter_value.param_value_code AND
    enquiry_attribute.param_type_code = 'CLST') as claim_status,
    (SELECT
    parameter_value.param_value_name
    FROM
    enquiry_attribute,
    parameter_value
    WHERE
    enquiry_attribute.enquiry_number = central_enquiry.enquiry_number AND
    enquiry_attribute.param_type_code = parameter_value.param_type_code AND
    enquiry_attribute.param_value_code = parameter_value.param_value_code AND
    enquiry_attribute.param_type_code = 'CLDT') as damage_to
    FROM
    central_enquiry

    I have put them in each of their own select statements. I am not sure if they change dependent on the attribute type (like if a date) but you should be able to pick this out of the wizard.

    Sorry if this is isn't what you mean!

    Laura



    ------------------------------
    Laura Perham
    Ringway Jacobs Ltd
    Chelmsford
    ------------------------------



  • 3.  RE: SQL report - rows to columns

    Posted 12-07-2020 16:23
    Hi Laura,

    Many thanks for this, it does exactly what I am looking for.

    ------------------------------
    Steven Broadfoot
    North Lanarkshire Council

    ------------------------------



  • 4.  RE: SQL report - rows to columns

    Posted 12-08-2020 03:42
    Hi Steven,

    Thanks for letting me know, pleased I could help.

    Laura

    ------------------------------
    Laura Perham
    Ringway Jacobs Ltd
    Chelmsford
    ------------------------------