hi, i want to know how to do that:
table: costtypes
fields : typecode
values: amount, vat, duty,
table bills:
fields: billid, costtype, amount
value
bi1 amount 1000
bi1 vat 10
bi2 amount 200
i need to create a view that will display amount, vat, duty as fields. note that these are values in the table.
so that i will have
billid amount vat duty
bi1 1000 10 0
bi2 200 0 0
thanks
-- Create #Bills table and values
Create Table #CostTypes (TypeCode varchar(10))
Go
Insert Into #CostTypes
Select 'Amount'
Union
Select 'Vat'
Union
Select 'Duty'
Go
-- Create #Bills table and values
Create Table #Bills (BillId int, CostType varchar(10), value int)
Go
Insert Into #Bills
Select 1, 'Amount', 1000
Union
Select 1, 'Vat', 10
Union
Select 2, 'Amount', 200
Go
-- Create view (you could use this code in a table function)
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select TypeCode From #CostTypes
Set @.stmt = 'Select Distinct BillId'
Open Fields
Fetch Next From Fields Into @.fieldname
While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End
Close Fields
Deallocate Fields
Set @.stmt = @.stmt + ' From #Bills'
Exec(@.stmt)
I hope it's useful
Giulio
|||Thats too good . can you explain whats happenin in the code though . only the fetch etc part|||
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select Amounttype From Amounttypes
Set @.stmt = 'Select Distinct BillId'
Open Fields
Fetch Next From Fields Into @.fieldname
While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End
Close Fields
Deallocate Fields
Set @.stmt = @.stmt + ' From Bill'
Exec(@.stmt)
|||
billid amount vat duty
699190BD-1410-47A8-AFAD-05EC38FEDBF8 0.00 50.00 0.00
B9B71063-D2AD-4735-8CC0-106335990A93 1.00 0.00 0.00
48A82C3C-CF64-4202-B6FA-5DD5A3D32D3C 0.00 0.00 210.00
|||
Sorry, I forgot table function definition requires you know returned table structure.
You could create a scalar function to get SQL Select statement and use this to get data...
Bye,
Giulio
|||
Declare @.fieldname varchar(10) -- variable used to get field value from AmountTypes table and use it as field name in your view
Declare @.stmt varchar(4000) -- variable that contains sql select statement to get data
Declare Fields Cursor For Select Amounttype From Amounttypes -- cursor to scan AmountTypes records and get view fields list
Set @.stmt = 'Select Distinct BillId' -- initialize @.stmt.
Open Fields -- Open Fields cursor
Fetch Next From Fields Into @.fieldname -- get first record
While @.@.Fetch_Status = 0 Begin -- while reading AmountTypes...
Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname -- ...add field to select fields list
Fetch Next From Fields Into @.fieldname -- read next record
End
Close Fields -- close Fields cursor
Deallocate Fields -- remove Fields cursor
Set @.stmt = @.stmt + ' From Bill' -- add FROM clause to select statement
Exec(@.stmt) -- execute select
At the end of code below, your select statement should be something like
Select Distinct BillId, amountfc * Case When AmountType = 'Amount' Then 1 Else 0 End As Amount, amountfc * Case When AmountType = 'Vat' Then 1 Else 0 End As Vat, amountfc * Case When AmountType = 'Duty' Then 1 Else 0 End As Duty From Bill
Simply I read AmountTypes to get fields list. Since you have AmountType in Bill as value, I multiply amountfc by 1 if AmountType is equal to current cursor's AmountType or by 0 else.
Ok?
|||
ca101 AD2574DB Estimated 0.00 0.00 2100.00
ca101 AD2574DB Estimated 3000.00 0.00 0.00
ca101 7BB1C98B Cheque 0.00 1.00 0.00
ca101 18ACADF5 Cheque 0.00 0.00 500.00
ca101 18ACADF5 Cheque 0.00 1100.00 0.00
nmh500 AD2574DB Estimated 0.00 0.00 10.00
nmh500 AD2574DB Estimated 2000.00 0.00 0.00
okay if u see the rows., those in same colour should have been on the same row, so
ca101 AD2574DB Estimated 3000.00 0.00 2100.00
code i used follows;
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select Amounttype From Amounttypes
Set @.stmt = 'Select pono, myid, billtype'
Open Fields
Fetch Next From Fields Into @.fieldname
While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End
Close Fields
Deallocate Fields
Set @.stmt = @.stmt + ' From multiplebillsviewall'
Exec(@.stmt)
|||
sql never got so interesting in my life.
i know mere basics u know thanks millions.
|||
Sorry, I forgot it...
Set @.stmt = @.stmt + ', Sum(Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname
and
Set @.stmt = @.stmt + ' From #Bills Group By BillId '
it should be ok...
|||
ca101 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 3000.00 0.00 2100.00
ca101 7BB1C98B-923B-471D-AC86-9F331671F789 Cheque 0.00 1.00 0.00
ca101 18ACADF5-E7D0-4B97-B79D-E266F07C804D Cheque 0.00 1100.00 500.00
nmh500 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 2000.00 0.00 10.00
lovely bra.
thanks.
by the way how do i create a view out of this now?
i will simply use the view in .net and display .
|||
okay no problemo i created a stored procedure.
views cannot contain declare, set etc..
but if the possibility that i can create a view out of this exists . let me know.
shot bra SQL ROCKS
|||
I don't think this possibility exists...
|||so whats the next proposal .. i only need to be able to bring the data to my application in .net and display it in a datagridview.|||
you could simply execute a select statement got by calling a scalar function that returns it...
Create Function dbo.xGetAmountSelectStmt()
Returns Varchar(4000)
As Begin
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select AmountType From AmountTypes
Set @.stmt = 'Select BillId'
Open Fields
Fetch Next From Fields Into @.fieldname
While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', Sum(Value * Case When AmountType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End
Close Fields
Deallocate Fields
Set @.stmt = @.stmt + ' From Bills Group By BillId '
Return(@.stmt)
End
When you have to get data, get below function return value and use it as select statement...
If you need to inject fields, join, where etc you could may simply modify last Set @.stmt as
Set @.stmt = @.stmt + '/*Select*/ From Bills /*Joins*/ /*Where*/ Group By BillId /*Group*/ /*Order*/'
and replace these optional pseudo-tags as your needs
/*Select*/ -> Other fields to get using joins or as static values beginning with ","
/*Joins*/ -> Table joins
/*Where*/ -> Where clause
/*Group*/ -> Other fields used in Group By beginning with ","
/*Order*/ -> Order By clause
Since they are delimited by /*..*/ if you don't need to use tags below, you may leave they as are
I think this could be better (more readable, simple and clean) than execute all every time... And more, if you have to change anything in function code, you only have to modify server-side function...
