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.
Author |
Topic |
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2013-10-04 : 21:30:12
|
Hey guys,I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data. Any ideas?declare @Stop table (StopId int, UserField varchar(20))declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))declare @UpdateSource table (HasPathway varchar(10))insert into @Stop (StopId, UserField)values (1, 'Yes')insert into @FieldDescription (Label, ColumnName)values ('HasPathway', 'UserField')insert into @UpdateSource (HasPathway)values ('No')I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns. |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2013-10-06 : 23:11:56
|
thanks Khtan,I'm generating a script using information_schema.columns to update columns in the target table from the source. I'll try to wrap it up in dynamic sql. thanks for the tip! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-07 : 05:40:33
|
[code]SELECT *FROM(SELECT m.StopId,m.Col,v.ValFROM(SELECT StopId,Col,ValFROM @stop sUNPIVOT (Val FOR Col IN ([UserField],..any other columns you want))u)mJOIN @FieldDescription fdON fd.ColumnName = m.ColJOIN (SELECT Col,ValFROM @UpdateSource uUNPIVOT (Val FOR Col IN ([HasPathway],..any other columns you want))u)nON n.Col = fd.Label)tPIVOT (MAX(Val) FOR Col IN (([UserField],..any other columns you want))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|