Fill Down - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Fill Down

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-24 : 08:27:45

How do i fill down the value in the n @columns?

@col1,@col2,@col3,@col4
2,2,3,4
?,3,4,5
?,?,3,4
?,?,?,?

Therefore, I want the last value that is not null in the respective column to fill the NULLS with that same value.

Example above:
@col1,@col2,@col3,@col4
2,2,3,4
2,3,4,5
2,3,3,4
2,3,3,4

what would the select statement look like?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 08:40:13

do you have a column that you used to ORDER BY to determine the sequence of the record display in the result ?

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-24 : 08:45:16

Yes it is the result of a dynamic column Pivot,So basically I insert the result of my Pivot into temp table ad then sort the result according to the Rows from Col1 witch is the date witch is variable).See below this script what result set looks like:
:

if exists (select * from ##temp1) drop table ##temp1

DECLARE @Cols VARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + colName + ']',
'[' + colName + ']')
FROM (
SELECT DISTINCT
CONVERT(VARCHAR,datecol,103) colName,convert(date,(max(Sort)),103) sort
FROM ctbl_IV_DemandandForecast_Waterfall_Storage
group by DateCol
) s
ORDER BY convert(date,Sort,103) ASC

DECLARE @query VARCHAR(MAX)
SET @query = N'SELECT *
into ##temp1
From
(SELECT daterow as DATE,datecol,ISNULL(qty,0) AS Qty
FROM ctbl_IV_DemandandForecast_Waterfall_Storage
Where qty IS NOT NULL
Group by daterow,datecol,Qty
) p
PIVOT
(
SUM(qty) FOR datecol IN
( '+
@cols +'
)) AS pvt '

EXECUTE(@query)

select * from ##temp1 t
inner join tbl_Bucket b
on BucketDescription=[DATE]
order by BucketEndDate ASC

end;

RESULTS:

DATE Jul-10 Aug-10 Sep-10 Oct-10
Jul-10 63848004 59987692 59987692 59987692
Aug-10 NULL 57988662 59987692 59987692
Sep-10 NULL NULL 56182784 59987692
Oct-10 NULL NULL NULL 55235385
Nov-10 NULL NULL NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 08:59:26

maybe using a recursive cte ?


create table #temp 
(
	id		int,
	col1		int,
	col2		int,
	col3		int,
	col4		int
)
insert into #temp
select	1, 2,	 2,	3,    4 	union all
select	2, NULL, 3,	4,    5  	union all
select	3, NULL, NULL,  3,    4  	union all
select	4, NULL, 4,	NULL, NULL 	union all
select	5, NULL, NULL,	NULL, NULL

; with rcte as
(
	select	id, col1, col2, col3, col4
	from	#temp
	where	id	= 1

	union all

	select	t.id, 
		col1 = isnull(t.col1, r.col1),
		col2 = isnull(t.col2, r.col2),
		col3 = isnull(t.col3, r.col3),
		col4 = isnull(t.col4, r.col4)
	from	#temp t
		inner join rcte r	on	t.id = r.id + 1
)
select	*
from	rcte

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-25 : 00:28:16

Thanks, that looks like it could work.Just a question...if my ID (first column) is Date and I dont have a ID column? Can this then work or does it only word with Int?I have tried all but can't seem to make it work.

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-25 : 01:04:29

Thanks!It's working

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 01:33:47

quote:
Originally posted by Rheinhardt

Thanks, that looks like it could work.Just a question...if my ID (first column) is Date and I dont have a ID column? Can this then work or does it only word with Int?I have tried all but can't seem to make it work.

you can use row_number() and order by your date column to generate a sequential ID

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-25 : 01:55:42

Khtan,

Is it possible to right this rcte into the Dynamic Pivot (as above) ?So that I keep the the DATA and Column names Dynamic?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 02:34:45

quote:
Originally posted by Rheinhardt

Khtan,

Is it possible to right this rcte into the Dynamic Pivot (as above) ?So that I keep the the DATA and Column names Dynamic?

yes, why not ? It will not be clean and the code might be messy.

KH
[spoiler]Time is always against us[/spoiler]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-02-25 : 02:37:25

Can you maybe assist by giving an example as to where to put it in?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 02:53:05

just need to make that section of query from ;with . . . onwards dynamic

KH
[spoiler]Time is always against us[/spoiler]