Dynamic resulset to a view or inline function

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 2000 Forums

Transact-SQL (2000)

Dynamic resulset to a view or inline function

Author

Topic

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 07:09:34

I have a table structure similar to listed below


FrmTemplate

frmTemplatePK   frmTemplateName
100	          AAAAA
101	          BBBBBB

FrmTemplateFields

FrmTemplateFieldPK  frmTemplateFK  DefaultLabel  ValueConainerOrder
200			100		 Label1	     	0	
201			100		 Label2		1
202			101		 XLabel1	0
203			101	         XLabel2	1
204			101 		 XLabel3	2

This table contains the field definition and it references FrmTemplateFK

FrmValueContainer

FrmContainerPK    frmTemplateFK  Value1  Value2  Value3  Value4		
 300			100	       Val1    Val2
 301			101	       XVal1   XVal2  XVal3				

This Table holds the values of the defined fields. This again references FrmTemplateFK

I want to create a view/inline function which accepts frmTemplateFK and 
gives a result as listed below 

If I pass 100

FrmTemplateName  Label1 Label2
AAAAA            Val1    Val2

If 101 is Passed

FrmTemplateName  XLabel1 XLabel2 XLabel3
BBBBB            XVal1    XVal2	    XVal3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 07:58:53

Refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53384

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 08:54:27

Thanks for your reply.

I guess your solution is to fetch top n columns.

But in my case I need to convert rows in FrmTemplateFields into columns

with the corresponding values and resultant resultset should

be captured in a view.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 08:57:40

From your expected result I think that will give you the solution
Post your exact expected result

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 09:17:38

Sorry If I am confusing in my question.

I have given the expected result in my question.

I can give it once again.

I need to create a inline function which may take FrmTemplatePk as parameter and return me something like this.


FrmTemplateName  Label1 Label2
AAAAA             Val1    Val2

Here "Label1 and Labels" are the records/rows in "FrmTemplateFields" and the "Val1" and "Val2"

are records/rows in "FrmValueContainer"

Thanks for your help..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 09:30:53

Why do you want to do this?
Where do you use this result?

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 09:39:38

I need this for reporting. I was planning for a view. I am not so sure about getting this
result in a single query. So I was planning either for a In-line UDF / Multi-statement UDF.
In-line can take only a statement and for Multi-statement UDF we have to define a table for result.
The constraint in the table is we have to define the columns for the result.
In my case the number of columns for the result vary. So I am bit confused about which approach to go on.

I am very much new-bie for advance db programming though I am familiar with SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 09:42:17

Which Reporting Tool are you using?
I think you can hide the columns at runtime by checking the values
If values are Nulls then suppres them

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 09:46:20

The problem here is I need to just prepare this Data pump(View).

This will be used by couple of applications which expects results in that format.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 09:50:20

Instead of doing this in View why cant you use Suppress option of Reports?
I think that will be easiest way than doing this in SQL Server View
What is your Reporting Tool?

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-19 : 10:11:36

I dont have a control over the reporting. A view is expected in that format. This is view will

be used by other application which expects this format. Is there a complex/simple way to approach this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 10:50:34

>>But in my case I need to convert rows in FrmTemplateFields into columns

Do you need Cross Tab?
Refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources