First time posting on the site so I hope I don't break any rules an开发者_如何学JAVAd someone can help me.
I have the following table:
machine_name variable_name variable_value
machine1 WAV 56789
machine1 WAV_CONT 5
machine1 AVI 67890
machine1 AVI_CONT 3
machine2 WAV 786579
machine2 WAV_CONT 20
machine2 AVI 182641
machine2 AVI_CONT 9
I am using arguments ($1). I would like to create a query that gives the following result when the user enters "wav" :
machine_name variable_name space count
machine1 WAV 56789 5
machine2 WAV 786579 20
Or this other result when the user enters "AVI"
machine_name variable_name space count
machine1 AVI 67890 3
machine2 AVI 182641 9
Is it possible to use just one argument for both fields? I mean, is there a way to concatenate $1 to the string "_CONT" so that the user only needs to enter "WAV" or "AVI" and get any of the results above?
Thanks
I would add a new field labeled "Type" in the table. Then you can use "WAV" or "AVI" to filter.
If you just need a query:
declare @param varchar(10)
set @param = 'WAV'
Select * from YOUR_TABLE where variable_name = @param
select * from my_table where variable name in (@param, @param + '_CONT')
You can use a poor-man's pivot:
select
machine_name,
@param as variable_name,
-- Turn rows into columns:
-- These expressions are zero for the rows we don't want.
sum(case when variable_name like '%_CONT' then 0 else variable_name end) as space,
sum(case when not variable_name like '%_CONT' then 0 else variable_name end) as count
from TABLE_NAME
group by machine_name
精彩评论