Help designing view

Hi folks,
I've got a view, illustrated at www.dbconsult.dk/ms/sqlquestion.jpg.
There must be a smarter way of doing it, but I can't figure it out:
I wish to get rid of the CASE's.
For each row in tblFejlantal I wish for each row in tblFejlKoder to be
listet as a column.
In the illustration the alias F10/F11/etc equals tblFejlKode.FejlKode,
but with a prefix of "F".
Thanks in advance for your efforts! ;-)
Regards /SnedkerIf you can't alter the design of this database and/or you can't or simply
don't want to do this kind of transformations on the client (I'm guessing
it's for presentation purposes) maybe you might find a few pointers in this
article by Itzik Ben-Gan:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML|||On Tue, 13 Sep 2005 04:19:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
If you have suggestions to the design, I'm all ears! Anything goes.
Regards /Snedker

>If you can't alter the design of this database and/or you can't or simply
>don't want to do this kind of transformations on the client (I'm guessing
>it's for presentation purposes) maybe you might find a few pointers in this
>article by Itzik Ben-Gan:
>http://www.windowsitpro.com/Article...15608.html?Ad=1
>
>ML|||Either post the requirements regarding this specific element of your system
or post DDL and some sample data.
In any case when designing a data-centric solution follow these basic
principles:
1) identifiy all entities, starting with the essential ones;
2) identify relationships between entities; and
3) identify attributes of all entities, with special attention to those
required by genuine business needs.
ML|||On Tue, 13 Sep 2005 05:17:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
Thanks for your response.
As for the database design, normalizing should be okay.
Let's say we have two tables, tblPrimary and tblForeign.
tblPrimary has three columns with one row:
PrimaryID ErrValue ForeignID
1 21.7 5
tblForeign has two columns with 5 rows
ForeignID ErrorCode
1 10
2 20
3 30
4 40
5 50
My view should look like:
PrimaryID Err10 Err20 Err30 Err40 Err50
1 0 0 0 0 21.7
If a row (6, 55) is added to tblForeign the result would be
PrimaryID Err10 Err20 Err30 Err40 Err50
Err55
1 0 0 0 0 21.7 0
I want each row in tblForeign represented in my view as a column.
/Snedker

>Either post the requirements regarding this specific element of your system
>or post DDL and some sample data.
>In any case when designing a data-centric solution follow these basic
>principles:
>1) identifiy all entities, starting with the essential ones;
>2) identify relationships between entities; and
>3) identify attributes of all entities, with special attention to those
>required by genuine business needs.
>
>ML|||Based on the sample you posted I strongly believe the solution described in
Itzik's article is the way to go for you.
Now, turning it into a view is a bit trickier. One way that I can think of
(and works) is a not-really-best-practice solution involving OPENQUERY.
E.g.:
create view dbo.CrossTabbedView
as
select <column list>
from openquery(<server_name>, 'exec <procedure name> <parameters>')
As I said it might work as expected, yet it's pretty resource-intensive. On
the other hand, if you expect it to be fast, it may not "work as expected".
:)
ML