Thursday, January 7, 2021

SQL13 How to Fill/populate data until next value

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
	) t

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

No comments:

Post a Comment

web stats