Tsql procedure to mutiple rows if it has duplicate id
-
hi i have a table with columns id,value if id is unique (only one time it exists in the table value column data has to column as it is in expected result column but if it is more than one time second column id value has to multiple with first column id and save it value in expected result column
id value
1 2
1 3
1 4
2 5
2 6
3 7
above is my source table if id is duplicate it should multiply and get the result as shown below
output column
id value expected result
1 2 2
1 3 6
1 4 24
2 5 5
2 6 30
3 7 7
can any one help ............:-)
-
First what you need to do is to follow this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
As posted before, you should give your sample data in a consumable format. This time was easy so I did it for you.
I recommend you to read Jeff Moden's article on Running totals http://www.sqlservercentral.com/articles/T-SQL/68467/
Now, here's the code:
DECLARE @TableTable(
idint,
valueint,
sum_valueint)
INSERT @Table(id, value) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 5),
(2, 6),
(3, 7);
DECLARE @Sumint
SET @Sum = 1;
WITH CTE AS(
SELECT id, value, ROW_NUMBER() OVER(Partition BY id ORDER BY id, value) rowno
FROM @Table)
UPDATE @Table SET
@Sum = sum_value = CASE WHEN rowno = 1 THEN t.value ELSE @Sum * t.value END
FROM @Table t
JOIN CTE a ON t.id = a.id AND t.value = a.value
SELECT *
FROM @Table
-
that will produce requested output based on what you explained so far:
declare @t table (id int, val int)
insert @t select 1, 2
insert @t select 1, 3
insert @t select 1, 4
insert @t select 2, 5
insert @t select 2, 6
insert @t select 3, 7
;with os
as
(select t1.*, row_number() over (partition by id order by val) rn
from @t t1
)
select t1.id, t1.val, t1.val * ISNULL(f.spval,1)
from os t1
outer apply (select
EXP(SUM(LOG(val))) spval
from os t2
where t2.id = t1.id
and t2.rn < t1.rn
group by t2.id) f
order by t1.id, t1.rn
-
DECLARE @T AS TABLE
(
id integer NOT NULL,
value integer NOT NULL,
result integer NULL,
PRIMARY KEY (id, value)
);
INSERT @T
(id, value)
VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 5),
(2, 6),
(3, 7)
SET NOCOUNT ON;
DECLARE
@LastID integer,
@Id integer,
@Total integer,
@Value integer;
DECLARE reader CURSOR
FORWARD_ONLY KEYSET SCROLL_LOCKS
FOR SELECT id, value FROM @T AS t ORDER BY t.id, t.value
FOR UPDATE OF value;
OPEN reader;
FETCH NEXT FROM reader INTO @Id, @Value;
SET @LastID = @Id;
SET @Total = 1;
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @Id = @LastID
BEGIN
SET @Total *= @Value;
END
ELSE
BEGIN
SET @Total = @Value;
SET @LastID = @ID;
END
UPDATE @T
SET result = @Total
WHERE CURRENT OF reader;
FETCH NEXT FROM reader INTO @Id, @Value;
END
CLOSE reader;
DEALLOCATE reader;
SELECT *
FROM @T;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul,
I'm curious. Why would you use a cursor?
Shouldn't it give the worst performance? (at least in my tests it did)
-
Luis,
If you prefer to use "quirky" update for calculating running-total, you better to follow all recommendations as per mentioned article eg. clustered index, maxdop and not duble -assignment (@var = col = [calculations] may not work as you expect) ...
I would stick with cross apply method here.
-
Luis Cazares (8/24/2012)
I'm curious. Why would you use a cursor?It's another option. Some people prefer them for some types of running totals problems pre-SQL Server 2012 (e.g. http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-redux.aspx).
I tend to do running totals with SQLCLR or Hugo Kornelis' set-based iteration (neither worth the effort for this question). Cursors do have quite linear performance (see e.g. http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals).
A friendly note about your solution: it does not follow the strict rules for the Quirky Update (see the article you linked to).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
After several runs (with consistent results) that might not be enough to prove something real. I found out that the explicit RBAR (from Paul) beated the hidden RBAR from Eugene. It was running even faster than the quirky update method I used, but then I used an index which made it run five times faster with 5000+ rows.
As noted by Eugene, the quirky update method needs to be used carefully and tested before using it, but the outer apply with EXP(SUM(LOG(value))) gave some unexpected results as well (values with a variation of .0001).
Paul's cursor is well tunned and it works great even with a larger set of data.
-
Luis Cazares (8/24/2012)
...the outer apply with EXP(SUM(LOG(value))) gave some unexpected results...Try it with zero or negative numbers in the 'val' column...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Luis Cazares (8/24/2012)
After several runs (with consistent results) that might not be enough to prove something real. I found out that the explicit RBAR (from Paul) beated the hidden RBAR from Eugene. It was running even faster than the quirky update method I used, but then I used an index which made it run five times faster with 5000+ rows.As noted by Eugene, the quirky update method needs to be used carefully and tested before using it, but the outer apply with EXP(SUM(LOG(value))) gave some unexpected results as well (values with a variation of .0001).
Paul's cursor is well tun
ned and it works great even with a larger set of data.Try converting it to a read-only fast-forward cursor, and change
WHERE CURRENT OF reader
to an explicit UPDATE with a supporting index.
Supporting article by Hugo Kornelis here.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (8/24/2012)
Try converting it to a read-only fast-forward cursor, and changeWHERE CURRENT OF reader
to an explicit UPDATE with a supporting index.
I didn't write it for performance (it's a cursor!) I fancied writing a one with slightly different options from usual. Call it artistic licence.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I don't know if this helped the OP, but it sure helped me to learn more.
Thank you guys.
-
Just for completeness, here's a recursive CTE way.
DECLARE @tTABLE(id INT,value INT)
INSERT @t(id, value)
VALUES (1, 2),(1, 3),(1, 4),(2, 5),(2, 6),(3, 7);
WITH CTE AS (
SELECT id,value,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) AS rn
FROM @t),
Recur AS (
SELECT id,value,rn, value AS prod
FROM CTE
WHERE rn=1
UNION ALL
SELECT c.id,c.value,c.rn, c.value * r.prod
FROM CTE c
INNER JOIN Recur r ON r.rn+1=c.rn AND r.id=c.id)
SELECT id,value,prod
FROM Recur
ORDER BY id,value;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
SQL Kiwi (8/24/2012)
ChrisM@Work (8/24/2012)
Try converting it to a read-only fast-forward cursor, and changeWHERE CURRENT OF reader
to an explicit UPDATE with a supporting index.
I didn't write it for performance (it's a cursor!) I fancied writing a one with slightly different options from usual. Call it artistic licence.
There are no shells. It needs shells.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply