pps: you're not executing PowerShell with xp_cmdshell. You're just starting an .exe file in a shell.[/quote]

You are absolutely correct here. I was working on a project few weeks ago involving...

I guess if you have a problem with using xp_cmdshell then Koen suggestion is quite good. You could create a generic job and write a stored procedure dynamically changing a...

Hi Koen

Thanks a lot for you comment.

The reason why I haven't used SQL job agent is because this solution has been implemented for a client using SQL server with...

I got it. Nested XML that's the right answer.

Thanks for all your help.

Final solution:

select

'04' as "@PaediatricCriticalCareStructure",

p.CriticalCareLocalIdentifier,

p.CriticalCareStartDate,

(select

p.ActivityDate,

...

Thanks again.

Nearly there. Still got a problem to put together in one group: ActivityDate & CriticalCareActivityCode

See the code below.

Mike

SELECT

'04' as...

Think I got in now (see the code below).

It's going to be painful to build dynamic pivot / SQL for the purpose of this task.

Any other solutions anyone?

Mike

select distinct

...

Thanks for you reply.

Ok. lets say that I've got dynamic pivot what do I do next?

Do I have to create dynamic sql to include all CriticalCareAcivityCodes?

Have a look at new...