mHelpMe mHelpMe - 13 days ago 5
SQL Question

make select query condition relative

I have two tables.

First table tblCPWgts,

cp nvarchar(10)
cDate date
weight float


Second table tblCP

cp nvarchar(20)
code nvarchar(5)


I currently have a query like below

select * from
(
select cp, cDate, weight from tblCPWgts where cDate >= '2014-09-09'
)source pivot(max(weight) for cp in ([AB], [CD], [EF]) as pvt order by cDate


this works fine. However the number of cp's will change in the future. So rather than hard code them in like above ([AB], [CD], [EF]) I would like to make use of the table tblCP where the column code has AB, CD, EF.

Is there anyway to adjust my query above so that it doesn't need the cp's hard coded? I tried below but after writing it realised it obviously won't work but that is what I'm trying to do.

select * from
(
select cp, cDate, weight from tblCPWgts where cDate >= '2014-09-09'
)source pivot(max(weight) for cp in (select code from tblCP) as pvt order by cDate

Answer

Select the codes into a variable and then use it to build your dynamic query.

declare @codes nvarchar(max) = ''

select @codes = @codes + '[' + code + '], '
from tblCP

set @codes = SUBSTRING(@codes, 1, LEN(@codes) - 1)

declare @q nvarchar(max)

set @q = 'select * from
 (
    select cp, cDate, weight from tblCPWgts where cDate >= ''2014-09-09''
 )source pivot(max(weight) for cp in (' + @codes + ') as pvt order by cDate'

exec(@q)