the (@sqlStr) seems to have worked, thanks

ok here's the completed solution, thanks to all who helped....

-- destroy staging table if exists

IF OBJECT_ID('tbl_TestTable', 'U') IS NOT NULL

begin

DROP TABLE tbl_TestTable

end

-- create the temp table

create table tbl_TestTable(

AccountId varchar(6),

StockId varchar(7),

[Jan...

Thats just what I was after. I can create the dynamic column string from here. Thanks.

perfect thanks

perfect thanks

got it....

just needed to add brackets to the @query variable when it's being executed to stop it being mistaken for a stored proc, so:

exec (@query)

i've created my query string but am getting another issue.

when i use

-- create the dynamic pivot

set @query = N'SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, ' +...

thanks both. really interesting stuff.

Dwain Camps, How to Design, Build and Test a Dynamic Search Stored Procedure was really useful.

just the job, thanks.

thanks, i'll check that out.

believe me, our anal codes have been the humour in many a meeting. 🙂

ahh i see, that worked great, thanks

well thats 2 new things i've learnt, correlated query and cross apply. both of which i can see myself using again.

thanks again 🙂

cracked it!!!

i never wanted to use the cost in the volume table. it should always come from the costing table.

so....

--the query

SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,

CASE

WHEN Cost IS...

thanks again

the reason i need this is so that i can have a cost for a product now (say, Jan) which i know is going to have a cost change...

getting closer. sorry, if i add a 3rd costing i get the second cost carried forward. so...

Year.........MonthNo.....Volume.....Cost

2013........1................5000........1

2013........2................4000........1

2013........3................5000........1.5

2013........4................2000........1.5

2013........5................5000........1.5

2013........6................3000........1.5

2013........7................7000........1.5

2013........8................5000........1.5

2013........9................3000........1.5

2013........10................7000........1.62

2013........11................5000........1.5

2013........12................9000........1.5

--the query

SELECT s.Year, s.Customer, s.StockCode, s.MonthNo, s.Volume,

CASE

WHEN Cost IS NULL THEN (SELECT...