• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

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:
10, clinic specific data, -1, summerised data

Open in new window


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.

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

Open in new window

0
soozh
Asked:
soozh
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the table structure and a sample of data? And also an example of the expected output?
0
 
macarrillo1Commented:
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.
0
 
John_VidmarCommented:
If you don't mind having 2 rows:
SELECT 	*
FROM	MyTable
WHERE	clinicid IN (@clinicid,-1)

Open in new window

If both records are needed on the same row:
SELECT	*
FROM	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = @clinicid
	) a
CROSS
JOIN	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = -1
	) b

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now