Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId =
2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:

> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
[vbcol=seagreen]
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and
B
> been converted to AttributeType) , there is probably a way to do it withou
t
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the O
LD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
>