Ok I think I have a better way of doing this.

I'm using a dynamic pivout query so I hope this helps

DROP TABLE #Yourtbl

CREATE TABLE #Yourtbl

(PK INT,

Restaurant VARCHAR(100),

RoomStatus ...

does my last reply not work?

HI there,

Does anyone have any ideas or alternative methods?

There is another problem in that how does your query know which related intv_schedule.program_Id gets which ex.Program_ID?

I think this is in fact what you looking for:

UPDATE s

SET program_id = e.program_ID

FROM...

HHI,

There are a few places you went wrong I'll try and explain.

For simplicity I'll just look at your select statements.

[Code]

SELECT e.program_id

FROM (SELECT *

...

Thanks for the reply 🙂

I guess it comes down to test test and test again 🙂

mmmm ooops,

I'll work on the performance and try and get back to you 🙁

Hi 🙂

When you say it gets lost?!!? does it throw an Error?

could you let me know what you mean, and send through an example as well .

Thanks

Chris

I'm not sure what you mean about Hardcoding the names?

None of my code should be hard coded, the only section that was hard coded was the creating of the data...

Hi there,

Ok I think a recursive CTE might get you what you want.

DECLARE @Yourtbl TABLE

(PK INT,

Restaurant VARCHAR(100),

RoomStatus VARCHAR(100))

INSERT INTO @Yourtbl

SELECT 10,'Yonos Restaurant','Room A- Incomplete Certification' UNION ALL

SELECT 10,'Yonos Restaurant','Room...

Glad you managed to find the answer.

There are a few articles about nulls on this site.

here is one of them

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

I tend NEVER EVER to use 'something' = null

😉

HI Paul,

Please could you give us the table schema and the proc schema with example input and outputs.

Otherwise it is going to be very very hard for us to help...

HI GSquared,

Don't get me wrong, I'm not a 100% but I have always been taugh both at work and here that cursors are a no-no where ever possible.

Perhaps because of...

Here are few examples of how to do this differently depending what you want.

1 - example is yours.

2 - example is just returning the first word before the string like...

HI,

Might be worth looking into changing that code to use a Tally table which should give you must better performance 😉

also something good to learn if you don't knwo much...