SQLTeam.com | Weblogs | Forums

How to ceate this query without a loop?


#1

I have a table that has many records with the location as MHB. What I would like to do is a query that grabs all the records with MHB and insert them into another table BUT now I would like to separate all the MHB records into MHB1, MHB2, MHB3 and MHB4.
So now I lets say I have 6 records with MHB as the location in the table:
Location Source
MHB 2
MHB 2
MHB 3
MHB 4
MHB 5
MHB 6

Then when I insert these records in the new table it should look like this:
Location Source
MHB1 2
MHB2 2
MHB3 3
MHB4 4
MHB1 5
MHB2 6

Is it possible to do this without using the cursor loop or any loop for that matter?

Thanks for the help


#2
;WITH cte AS
(
	SELECT Location, [Source],
		ROW_NUMBER() OVER (ORDER BY SomeOrderingColumns)-1 AS RN
	FROM
		YourSourceTable
)

INSERT INTO YourDestinationTable
( [Location], [Source] )
SELECT
	[Location] + CAST( RN%4+1 AS CHAR(1)), [Source]
FROM
	cte;

You have to replace the placeholder "SomeOrderingColumns" with some column names to order the rows.


#3

Thank you so much answered my question perfectly.