Dynamic Query - 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 2005 Forums

Transact-SQL (2005)

Dynamic Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-24 : 08:21:45

Snehalata writes "i have a #table as follows

CREATE TABLE #ReportingNumbers
(
HeadID INT,
EntityID INT,
Type VARCHAR(10),
Value DECIMAL(30,12)
)
a master table as follows

CREATE TABLE HeadIDFieldMapping
(
HeadID INT,
FieldName VARCHAR(100)
)

and one transaction table (vData) which have columns as all rows of table HeadIDFieldMapping i.e.table vData is having FieldName which are rows(value of fieldName) in table HeadIDFieldMapping . table #ReportingNumbers has field HeadID and table HeadIDFieldMapping has field HeadID.

now i want to update field Value of #ReportingNumbers Table with the value of fields of vData table using dynamic query. how can be this done?"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 09:24:04

please post the structure for vData and sample data for all 3 tables with the expected result

snehsoni
Starting Member

3 Posts

Posted - 2007-01-25 : 02:20:39

table structure for #ReportingNumbers
CREATE TABLE #ReportingNumbers
(
HeadID INT,
EntityID INT,
Type VARCHAR(10),
Value DECIMAL(30,12)
)

table structure for HeadIDFieldMapping
CREATE TABLE HeadIDFieldMapping
(
HeadID INT,
FieldName VARCHAR(100)
)

and table structure for vData as follows

CREATE TABLE vData
(
FundID INT,
InvestorID INT,
AccountingPeriodDate DATETIME,
BeginUnits DECIMAL(30,12),
BeginAdditionUnits DECIMAL(30,12),
BeginAdditionAmount DECIMAL(30,12),
EndingAdditionAmount DECIMAL(30,12),
GrossRealizedGain DECIMAL(30,12),
BrokerCommission DECIMAL(30,12),
NetRealizedGain DECIMAL(30,12),
OperatingExpense DECIMAL(30,12),
OfferingsellingExpense DECIMAL(30,12),
OrganizationalExpense DECIMAL(30,12),
USObligationNonTaxableInterestIncome DECIMAL(30,12),
BrokerBankBalanceInterestIncome DECIMAL(30,12),
FixedIncomeInterestIncome DECIMAL(30,12),
CapitalGain DECIMAL(30,12),
SellingFee DECIMAL(30,12),
SellingManagementFeeMidQuarter DECIMAL(30,12),
SyndicateCost DECIMAL(30,12),
BeginNetCapital DECIMAL(30,12),
DomesticDividendExpense DECIMAL(30,12),
FixedIncomeinterestexpense DECIMAL(30,12),
ForeignTaxWithheld DECIMAL(30,12),
DomesticDividendIncome DECIMAL(30,12),
RealizedGainShortTerm DECIMAL(30,12)

)

i have following records in table HeadIDFieldMapping

HeadID FieldName

1 BeginUnits
3 BeginAdditionUnits
4 BeginAdditionAmount
5 EndingAdditionAmount
6 GrossRealizedGain
7 BrokerCommission
8 NetRealizedGain
10 OperatingExpense
11 OfferingsellingExpense
12 OrganizationalExpense
13 USObligationNonTaxableInterestIncome
14 BrokerBankBalanceInterestIncome
15 FixedIncomeInterestIncome
16 CapitalGain
17 SellingFee
18 SellingManagementFeeMidQuarter
19 SyndicateCost
20 BeginNetCapital
21 DomesticDividendExpense
22 FixedIncomeinterestexpense
23 ForeignTaxWithheld
24 DomesticDividendIncome
25 RealizedGainShortTerm

in table #ReportingNumbers i have field HeadID, now how can i update value for headID from vData table using dynamic query having join from these two tables (vData,HeadIDFieldMapping)?

sneha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:26:38

Sample data for ALL three tables, please...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:27:23

And if you care enough for a proper answer, you will also post your expected output based on the sample data you provide.

Peter Larsson
Helsingborg, Sweden

snehsoni
Starting Member

3 Posts

Posted - 2007-01-29 : 02:06:44

sample data for table HeadIDFieldMapping

HeadID FieldName

1 BeginUnits
3 BeginAdditionUnits
4 BeginAdditionAmount
5 EndingAdditionAmount
6 GrossRealizedGain
7 BrokerCommission
8 NetRealizedGain
10 OperatingExpense
11 OfferingsellingExpense
12 OrganizationalExpense
13 USObligationNonTaxableInterestIncome
14 BrokerBankBalanceInterestIncome
15 FixedIncomeInterestIncome
16 CapitalGain
17 SellingFee
18 SellingManagementFeeMidQuarter
19 SyndicateCost
20 BeginNetCapital
21 DomesticDividendExpense
22 FixedIncomeinterestexpense
23 ForeignTaxWithheld
24 DomesticDividendIncome
25 RealizedGainShortTerm

sample data for table #ReportingNumbers
HeadID EntityID Type Value
1 1 'IN' NULL
3 1 'IN' NULL
4 1 'IN' NULL
5 1 'IN' NULL
6 1 'IN' NULL

sample data for table vData
FundID InvestorID AccountingPeriodDate BeginUnits BeginAdditionUnits BeginAdditionAmount EndingAdditionAmount GrossRealizedGain
1 1 '1/1/5' 10 10 100.20 120.3 20

now i want to update table #ReportingNumbers (value field) as follows

HeadID EntityID Type Value
1 1 'IN' 10
3 1 'IN' 10
4 1 'IN' 100.20
5 1 'IN' 120.3
6 1 'IN' 20

how it can be done?

sneha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 02:42:28

[code]
drop table #ReportingNumbers
drop table HeadIDFieldMapping
drop table vData

-------------------------------------------
-- Prepare the table & data for testing
-------------------------------------------
create table #ReportingNumbers
(
HeadID INT,
EntityID INT,
Type VARCHAR(10),
Value DECIMAL(30,12)
)

create table HeadIDFieldMapping
(
HeadID INT,
FieldName VARCHAR(100)
)

create table vData
(
FundID INT,
InvestorID INT,
AccountingPeriodDate DATETIME,
BeginUnits DECIMAL(30,12),
BeginAdditionUnits DECIMAL(30,12),
BeginAdditionAmount DECIMAL(30,12),
EndingAdditionAmount DECIMAL(30,12),
GrossRealizedGain DECIMAL(30,12),
BrokerCommission DECIMAL(30,12),
NetRealizedGain DECIMAL(30,12),
OperatingExpense DECIMAL(30,12),
OfferingsellingExpense DECIMAL(30,12),
OrganizationalExpense DECIMAL(30,12),
USObligationNonTaxableInterestIncome DECIMAL(30,12),
BrokerBankBalanceInterestIncome DECIMAL(30,12),
FixedIncomeInterestIncome DECIMAL(30,12),
CapitalGain DECIMAL(30,12),
SellingFee DECIMAL(30,12),
SellingManagementFeeMidQuarter DECIMAL(30,12),
SyndicateCost DECIMAL(30,12),
BeginNetCapital DECIMAL(30,12),
DomesticDividendExpense DECIMAL(30,12),
FixedIncomeinterestexpense DECIMAL(30,12),
ForeignTaxWithheld DECIMAL(30,12),
DomesticDividendIncome DECIMAL(30,12),
RealizedGainShortTerm DECIMAL(30,12)
)

insert into HeadIDFieldMapping
select 1, 'BeginUnits' union all
select 3, 'BeginAdditionUnits' union all
select 4, 'BeginAdditionAmount' union all
select 5, 'EndingAdditionAmount' union all
select 6, 'GrossRealizedGain' union all
select 7, 'BrokerCommission' union all
select 8, 'NetRealizedGain' union all
select 10, 'OperatingExpense' union all
select 11, 'OfferingsellingExpense' union all
select 12, 'OrganizationalExpense' union all
select 13, 'USObligationNonTaxableInterestIncome' union all
select 14, 'BrokerBankBalanceInterestIncome' union all
select 15, 'FixedIncomeInterestIncome' union all
select 16, 'CapitalGain' union all
select 17, 'SellingFee' union all
select 18, 'SellingManagementFeeMidQuarter' union all
select 19, 'SyndicateCost' union all
select 20, 'BeginNetCapital' union all
select 21, 'DomesticDividendExpense' union all
select 22, 'FixedIncomeinterestexpense' union all
select 23, 'ForeignTaxWithheld' union all
select 24, 'DomesticDividendIncome' union all
select 25, 'RealizedGainShortTerm'

insert into #ReportingNumbers(HeadID, EntityID, Type, Value)
select 1, 1, 'IN', NULL union all
select 3, 1, 'IN', NULL union all
select 4, 1, 'IN', NULL union all
select 5, 1, 'IN', NULL union all
select 6, 1, 'IN', NULL

insert into vData(FundID, InvestorID, AccountingPeriodDate, BeginUnits, BeginAdditionUnits, BeginAdditionAmount, EndingAdditionAmount, GrossRealizedGain)
select 1, 1, '1/1/5', 10, 10, 100.20, 120.3, 20

-------------------------------------------
-- Generate the dynamic query
-------------------------------------------

-- Query
declare @sql nvarchar(4000)

select @sql = ''
select @sql = @sql + 'update t' + char(13)
select @sql = @sql + 'set t.Value = case' + char(13)
select @sql = @sql + char(9) + 'when t.HeadID = ' + convert(varchar(10), t.HeadID) + ' then d.' + m.FieldName + char(13)
from #ReportingNumbers t inner join HeadIDFieldMapping m
on t.HeadID = m.HeadID
select @sql = @sql + char(9) + 'end' + char(13)
select @sql = @sql + 'from #ReportingNumbers t inner join vData d' + char(13)
select @sql = @sql + 'on t.EntityID = d.FundID' + char(13)

print @sql
/*
update t
set t.Value = case
when t.HeadID = 1 then d.BeginUnits
when t.HeadID = 3 then d.BeginAdditionUnits
when t.HeadID = 4 then d.BeginAdditionAmount
when t.HeadID = 5 then d.EndingAdditionAmount
when t.HeadID = 6 then d.GrossRealizedGain
end
from #ReportingNumbers t inner join vData d
on t.EntityID = d.FundID
*/

exec (@sql)

select * from #ReportingNumbers

[/code]

snehsoni
Starting Member

3 Posts

Posted - 2007-01-29 : 03:06:56

Thanks
but i need any other solution or way to convert it into dynamic query because i have more than 125 fields in vData table and very much complicated queries which i need to convert in dynamic queries.

sneha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:12:44

what is the reason that the query can't handle 125 fields ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 03:55:42

If you have 125 columns, there is something wrong with your table design.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources