Hi again,

I tried using the PIVOT and UNPIVOT script simplifying it to see if I could get rid of the error

DECLARE @SQLHead VARCHAR(8000)

DECLARE @SQLBody VARCHAR(8000)

DECLARE @SQLFoot VARCHAR(8000)

declare @tablename varchar(100)

--my test...

Well,

I tried doing this in Excel and managed to jig it to look right using the pivot table and charts, but there seems to be some kind of bug in...

Not got very far with this one.

DECLARE @SQLHead VARCHAR(8000)

DECLARE @SQLBody VARCHAR(8000)

DECLARE @SQLFoot VARCHAR(8000)

declare @tablename varchar(100)

--my test table is call DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES

set @tablename='DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES'

--===== Populate the variables using info from the table

SET @SQLHead...

I do have 2005 so Ill give it a go but this is something far advanced to what Im used too 🙁

Ill let you know how I get on. I...

Ive spent all day trying to figure out how to do this one but Im still stumped :sick:

I know that this is what I want to do though.....

I need to...

Your right Ill definitely end up aggregating at some point, although Ill probably want to go for 6 months or something.

The data will probably end up being used to...

Thats right,

This is only one part of a large table which will grow and grow every time we refresh the data set. Its to basically trace the quality of data...

Ahh I see,

OK Ill try again!

Ive tried amending the script for my own table

DECLARE @SQLHead VARCHAR(8000)

DECLARE @SQLBody VARCHAR(8000)

DECLARE @SQLFoot VARCHAR(8000)

--===== Populate the variables using info from the table

SET @SQLHead = 'SELECT'

SELECT @SQLBody = ISNULL(@SQLBody+',','') +...

thanks for that,

Ill have a try and see what I can do!

Debbie

And after all that I managed to get it working anyway :w00t:

SELECT Q05b.Title, Q05b.Percentage_Of, CAST(CAST(NULLIF(Q05b.PEP,0) AS Decimal)/CAST(NULLIF(Q05.PEP,0) AS Decimal) *100 AS Decimal)AS PEP, Q05b.Quality_Date

FROM #Q05b_PEP Q05b

INNER JOIN #Q05_PEP...

Of course,

I cant believe I didnt think of that

Thanks

Debbie

Im being a bit stupid but how does ,2, 8000 work?

Debbie

Ah I see. So the -1 means everything but the first character.

Thanks for that!

Debbie

Thanks for that,

I can think of a few other uses for this one so I will definitely give this a go!

Thanks again