Query syntax
Hello,
I have a table that contains rows of data for clinics. Basically each row has the clinicid and then a load of data regarding performance.
There is also a row where the clinicid is -1 (not a valid clinic) and this holds the summarised data for the whole table.
If I want to select a single clinics data, and the summarized data what would the general syntax be?
Give clinicid of 10 I am looking for:
Is the query something like the example below, or is there a better syntax
Given that the column names are clinicid, data1, data2, data3 etc.
I have a table that contains rows of data for clinics. Basically each row has the clinicid and then a load of data regarding performance.
There is also a row where the clinicid is -1 (not a valid clinic) and this holds the summarised data for the whole table.
If I want to select a single clinics data, and the summarized data what would the general syntax be?
Give clinicid of 10 I am looking for:
10, clinic specific data, -1, summerised dataIs the query something like the example below, or is there a better syntax
Given that the column names are clinicid, data1, data2, data3 etc.
Select
@clinicid as ClinicId
Case clinicid = @clinicid then data1 else null end as clinicData1,
Case clinicid = @clinicid then data2 else null end as clinicData2,
Case clinicid = @clinicid then data3 else null end as clinicData3,
-1 as Summary,
Case clinicid = -1 then data1 else null end as SummaryData1,
Case clinicid = -1 then data2 else null end as SummaryData1,
Case clinicid = -1 then data3 else null end as SummaryData1
From MyTable
Vitor Montalvão
Can you post the table structure and a sample of data? And also an example of the expected output?
I am not sure I understand what you are trying to do.
But perhaps you can use a subquery as follows:
Select ClinicID, Data, (Select ClinicID from MyTable where ClinicID=-1)
from MyTable
Where ClinicID <>-1
The subquery can pull up your summerized data along side your other records.
But perhaps you can use a subquery as follows:
Select ClinicID, Data, (Select ClinicID from MyTable where ClinicID=-1)
from MyTable
Where ClinicID <>-1
The subquery can pull up your summerized data along side your other records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.