Single Update instead of Cursor
-
I would like to rearrange data as follows
TableA (Col1 int, COl2 int, ...)
Primary key is Col1 + Col 2
Sample Data
Col1 Col2
123 2
123 5
123 9
124 4
127 12
127 20
Required Data
Col1 Col2
123 1
123 2
123 3
124 1
127 1
127 2
Can this be done in a single update instead of using a cursor ?
-
Check this out, the first post as 2 possible solutions. I'd go for the 2nd one if this is gonna run more than once.
-
The query below gives the following result, and is safe to run.
Col1 Col2
----------- -----------
123 1
123 2
123 3
124 1
127 1
127 2
CREATE TABLE #TableA (Col1 int, COl2 int)
INSERT INTO #TableA
SELECT 123, 2 UNION ALL
SELECT 123, 5 UNION ALL
SELECT 123, 9 UNION ALL
SELECT 124, 4 UNION ALL
SELECT 127, 12 UNION ALL
SELECT 127, 20
SELECT Col1, COUNT(Col2) AS Col2 FROM (
SELECT a.Col1, a.Col2
FROM #TableA a INNER JOIN #TableA b ON a.col1 = b.col1 AND a.col2 >= b.col2
) c
GROUP BY Col1, Col2
ORDER BY Col1, Col2
DROP TABLE #TableA
Regards,
Ryan
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
That's precisely the last query shown in my link...
. -
So it it! I was put off by all those words...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Not the first time that happens
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply