Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Sum then delete one row
CREATE TABLE #temp( Item VARCHAR(max), Qty DECIMAL(18,2), FixItem VARCHAR(max) ); INSERT INTO #temp(Item, Qty, FixItem) VALUES ('ItemRandom-1', 2, 'ItemA'), ('ItemRandom-2', 5, 'ItemA'), ('ItemRandom-3', 5, 'ItemB'), ('ItemRandom-4', 5, ''), ('ItemRandom-5', 5, ''); WITH t AS ( SELECT *, SUM(Qty)OVER(PARTITION BY FixItem) t, ROW_NUMBER()OVER(PARTITION BY FixItem ORDER BY Item) n FROM #temp WHERE FixItem != '' ) MERGE t USING (SELECT * FROM t WHERE n = 1) s ON t.Item = s.Item AND t.FixItem = s.FixItem AND t.n = s.n WHEN MATCHED THEN UPDATE SET t.Qty = s.t WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.*; SELECT * FROM #temp;
run
|
edit
|
history
|
help
0
database size
Hospital
NAMES CODE
2021-03-08_LeetCodeSQL
add-empty-rows-after-certain-records-in-a-table
exp7
libros
Print 10 records
28-02 3
for xml path concatenation ordered by seqno