开发者

SQL - Concatenate string to arguments ( $1 )

开发者 https://www.devze.com 2023-02-17 05:46 出处:网络
First time posting on the site so I hope I don\'t break any rules an开发者_如何学JAVAd someone can help me.

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号