Sample output

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...