Dynamic- Fill missing value - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic- Fill missing value

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2009-09-29 : 05:10:14

/**
I am using http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115682 from Peso to fill values.
How to fill columns with one "dynamic" sql-query ?

I am using source --> pivot --> longitudinell dataset
Number of Columns can changed (user select columns before pivot).
All Column names are avaible from a table.

1 1925-02-02 a b 1
1 1928-03-15 empty c 4
1 1930-05-22 b empty 9
1 1935-11-13 empty d 3

Result:
1 1925-02-02 a b 1
1 1928-03-15 a c 4
1 1930-05-22 b c 9
1 1935-11-13 b d 3

**/

-- after pivot
create table #pivottable
(
individualNo int,
ValueDate date,
Column1 nvarchar(50),
Column2 nvarchar(50),
Column3 nvarchar(50),
)
insert into #pivottable values(1,'1925-02-02' , 'a' , 'b','1')
insert into #pivottable values(1,'1928-03-15' , '' , 'c','4')
insert into #pivottable values(1,'1930-05-22' , 'b' , '','9')
insert into #pivottable values(1,'1935-11-13' , '' , 'd','3')
select * from #pivottable order by valuedate

-- column list with all possible columns
create table #selectColumn
(
ColumnName nvarchar(50),
fillColumn int
)
insert into #selectColumn values('Column1' , 1)
insert into #selectColumn values('Column2' , 1)
insert into #selectColumn values('Column3' , 0)
insert into #selectColumn values('Column4' , 1)
insert into #selectColumn values('Column5' , 0)
select * from #selectColumn

-- fill columns where selectColumns = 1

DECLARE @IndividualNo int,
@a nvarchar (50)

SELECT top 1 @a = ColumnX,
@IndividualNo = IndividualNo
from #pivottable
ORDER BY IndividualNo,
ValueDate

update #pivottable
set ColumnX = COALESCE(NULLIF(ColumnX, null), @a),
@a = case when IndividualNo = @IndividualNo then coalesce(nullif(ColumnX, null), @a) else ColumnX end,
@IndividualNo = IndividualNo

select * from #pivottable order by valuedate

drop table #pivottable
drop table #selectColumn

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 05:49:10

WHILE (SELECT blaa FROM blaa WHERE blaa blaa = 1 ) = 1

BEGIN
Update table SET Blaa blaa SET @Counter = @Counter + 1
IF (SELECT blaa FROM blaa) < 1
BREAK
ELSE
CONTINUE
END

You looking for that kind of thing?

[ /fail at query]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:01:46

sql 2005, use below


SELECT t.individualNo,t.valueDate,
COALESCE(t.Column1,a.Column1) AS Column1,
COALESCE(t.Column2,b.Column2) AS Column2,
COALESCE(t.Column3,c.Column3) AS Column3
FROM yourtable t
OUTER APPLY(SELECT TOP 1 Column1
            FROM YourTable
            WHERE individualNo=t.individualNo
            AND valueDate<t.valueDate
            AND Column1 IS NOT NULL
            ORDER BY valueDate DESC)a
OUTER APPLY(SELECT TOP 1 Column2
            FROM YourTable
            WHERE individualNo=t.individualNo
            AND valueDate<t.valueDate
            AND Column2 IS NOT NULL
            ORDER BY valueDate DESC)b
OUTER APPLY(SELECT TOP 1 Column3
            FROM YourTable
            WHERE individualNo=t.individualNo
            AND valueDate<t.valueDate
            AND Column3 IS NOT NULL
            ORDER BY valueDate DESC)c

Clas
Starting Member

33 Posts

Posted - 2009-09-29 : 06:54:12

ColumnName = ColumnName is the Value (Column1, Column2....) from another table
The ColumnNames can change, Column1, Column2 or Column1, Column3,Column4,Column5

create table #ColumnNamesInPivot
(
ColumnName nvarchar(50)
)
insert into #selectColumn values('Column1')
insert into #selectColumn values('Column2')
insert into #selectColumn values('Column3')

#ColumnNamesInPivot has all the Columnnames in the pivot-table
#selectColumn has all possible Columnnames and value if the column chould be "filled"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources