I have this solved in case anyone is wondering how to get to the result I was looking for:

SELECT
 X.Y.value('(Seg/@Code)[1]', 'VARCHAR(50)') AS segCode
,Q.W.value('(LineVal/@Code)[1]', 'VARCHAR(50)') AS lineCode
,Q.W.value('(LineDate/.)[1]', 'DATE')...

Hi Jeff,

Sorry, I might be dim here - I can't use it without the subquery because I need the where clause on Survey_Answers  (limited to Survey_ID = 2)

The WHERE clause...

Thanks all - I thought there might just be some super simple reason like PIVOT just works better with in-memory data etc.

This example of demographics data takes < 1 second...

The honest answer to that would be "I think so.." - but I'd appreciate a confirmation if I'm on the right track.. if not, then if you could point out...

I received a reply from another source for this problem, so thought i'd add the solution here just in case it helps anyone else in the future

[code...

Luis posted a full solution a few seconds before I did including the 6 month limitation

Sean - its actually for a job that will dump the data into a new simplified table, only running once every week or 2.

Luis - that solution works perfectly -...

What I mean by not having control over the values is that I can't modify the original data, sorry for the confusion

I've made the ddl below, and made it a...

[edit: double posted - sorry!]

I might be taking this a little too simplistic - but I have the code below which will get you your averages by year and month - then just join...

I see where you're going with it - the mixed could be, for example

if PersonID 1 is mixed Caucasian and Hispanic, then they wouldn't be marked as 'Mixed Ethnicity' they...

Hi Wayne,

Thats the route I went down originally - but I got the error that nested case statements could only goto 10 levels

I had

CASE

WHEN ETH_CAUCASIAN = 'Yes' THEN '1'

WHEN...

At one point I did pivot, then unpivoted and then had a duh moment when i realized that the data was back how it started.

This is basically what I have...

You could create a linked server on each to the other - look under server objects in SSMS then you can query them like

SELECT

*

FROM myTable a

INNER JOIN [otherserver].[mydb].[dbo].[myTable]...

Thanks Dave - that worked like a charm - I actually went back to one of the views that was used in the query and added the latest_date there and...