Scenerio is Data is populate in table but in between some null occurance are available. Need to populate the in null starting from copy from 1 populate data and copy until next occurance of data and so on.
This can be done by various ways, few of the best way, solution writing in this post.
Data in Table
id | COL1 | COL2 |
---|---|---|
1 | A | B |
2 | ||
3 | ||
4 | ||
5 | ||
6 | A | C |
7 | ||
8 | ||
9 | ||
10 | A | D |
11 | ||
12 | ||
13 | ||
14 | ||
15 | ||
16 | ||
17 |
Expected Result
id | COL1 | COL2 |
---|---|---|
1 | A | B |
2 | B | |
3 | B | |
4 | B | |
5 | B | |
6 | A | C |
7 | C | |
8 | C | |
9 | C | |
10 | A | D |
11 | D | |
12 | D | |
13 | D | |
14 | D | |
15 | D | |
16 | D | |
17 | D |
SELECT * INTO SQL13 FROM ( SELECT 1 ID, 'A' COL1, 'B' COL2 UNION ALL SELECT 2, NULL, NULL UNION ALL SELECT 3, NULL, NULL UNION ALL SELECT 4, NULL, NULL UNION ALL SELECT 5, NULL, NULL UNION ALL SELECT 6 ID, 'A' COL1, 'C' COL2 UNION ALL SELECT 7, NULL, NULL UNION ALL SELECT 8, NULL, NULL UNION ALL SELECT 9, NULL, NULL UNION ALL SELECT 10 ID, 'A' COL1, 'D' COL2 UNION ALL SELECT 11, NULL, NULL UNION ALL SELECT 12, NULL, NULL UNION ALL SELECT 13, NULL, NULL UNION ALL SELECT 14, NULL, NULL UNION ALL SELECT 15, NULL, NULL UNION ALL SELECT 16, NULL, NULL UNION ALL SELECT 17, NULL, NULL ) test
--Solution 1 SELECT * , CASE WHEN COL2 IS NULL THEN ( SELECT TOP 1 COL2 FROM SQL13 WHERE ID < T.ID AND COL2 IS NOT NULL ORDER BY ID DESC ) ELSE COL2 END AS COL3 FROM SQL13 T--Solution 2 WITH CTE AS ( SELECT ID , COL2 , COALESCE(LEAD(ID) OVER ( ORDER BY ID ), ( SELECT COUNT(*) + 1 FROM SQL13 )) NXT_SEQ FROM SQL13 WHERE COL1 IS NOT NULL ) SELECT EP.ID , EP.COL1 , EP.COL2 --,EP.COL3 , ( SELECT CTE.COL2 FROM CTE WHERE EP.ID >= CTE.ID AND EP.ID < CTE.NXT_SEQ ) POPULATED FROM SQL13 EP; --Solution 3 SELECT c1.id AS id , coalesce(c1.col2, t.col2) FROM SQL13 c1 OUTER APPLY ( SELECT TOP 1 c2.col2 FROM SQL13 c2 WHERE c1.col2 IS NULL AND c2.col2 IS NOT NULL AND c2.id < c1.id ORDER BY c2.id DESC ) tTags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice
No comments:
Post a Comment