Please run the following code and post the first 8 (eight) lines of code.

It will only print the SQL statement, not execute it. I added USE MASTER to protect your...

Glad it worked out for you 😀

Once you figured how ROW_NUBER and its sibling (RANK and DENSE_RANK) work, all come in really handy. 😉

Did you even tried that code???

There...

That's one possible direction. But instead of guessing I'd like to know.

balbirsinghsodhi (3/3/2010)

---

I added char(13) also but no success..

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

Msg 111, Level 15, State 1, Line 2

'CREATE VIEW' must be the first...

It depends on several things:

What is the format the original values are stored as?

How do you want to display times before 10:00 (w/ or w/o leading zero)?

Why is'n it stored...

@gcresse:

If you compare ALZDBA's solution and mine you should focus on the different interpretation of where the ORDER BY section would apply to.

Also you'll see a difference if you have...

@steve-2:

The disadvantage of your solution is that you have to have a aclumn named "deed_name" within each table you want to apply that dynamic query to.

Therefore, I decided to use...

Please post the value of @sql and the error message.

Add a line break before your GO statements:

SET @SQL ='USE '+@DESDB+'

GO '

SET @SQL = @SQL+'

CREATE VIEW '+@NAME+' AS SELECT * FROM '+@BASEDB+'.DBO.'+@NAME+'

GO'

Unfortunately, your sample code won't run since you defined ID as being identity but you're trying to insert value 13 for every row.

Please clean up your sample code and run...

Would something like the following will do it?

Side note: this will only work if you always provide an ORDER BY clause since this is required for the ROW_NUMBER function....

SELECT @V_QUERY_STR...

First of all: thank your for taking the time to set up the sample data!!

Made it really easy to work on! Great job :Wow:

Here's what I came up with:

;WITH cte...

Would you please provide table def, some sample data and expected result? I'm sure it's possible to do it without dynamic sql but I'd like to have something to play...

Instead of that verbal description would you mind setting up some table definition (target table), input values that are provided and expected result set in your target table.

If you'd like...