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
------------------------------
Original Message:
Sent: 12-03-2020 17:13
From: Steven Broadfoot
Subject: SQL report - rows to columns
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
------------------------------