How to select two dimensional data

Question

How to select two dimensional data

nadarajan_v

SSC-Addicted

Points: 406
More actions
October 5, 2010 at 4:12 am

#239276

I have a sql server table with the following data:
PrimaryKey ForeignKey DataType Value
1 1 Actor abc
2 1 Movie efg
3 1 Movie hij
4 2 Actor mno
5 2 Movie pqr
6 2 Movie stu
7 2 Movie vwx
I want to select the data from this table and load a Csharp class, named Actors. The class has a property called ActorName and a collection of movies by the actor. Essentially I have to populate the ActorName with data in the Value column and collect all the movie names by the actor from the Value column and populat the collection. I am not sure how to do that, especially how to write the appropriate sql select sattement.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Is it too late to recommened that you change the structure of your table to be a normalized relational-database?

Because what you have there is an EAV table and this query you need to write will only be the begining of your problems if you continue with this structure

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 2

Unfortunately I could not normalize the table. I have to resolve it either at the select statement level or in the C# code.

Thanks

faheemahmad14 SSC Veteran Points: 266 More actions · Answer 3

IF your are using Datasets this can be possible by using Two Select statments

first statment will fetch all actors

and second statment will fetch all movies

hence returning two data tables in dataset

instantiate your class and fill values from first table and for each row in first table you can use datatable's select method to filter out movies.

which can be added in collection of movies.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Second Steve's sentiments about changing the structure of the table. Whether it's called an EAV or a MUCK, that design has been "invented" again and again over the years by people thinking it's a way to make a relational database more "flexible" or more object-oriented. The design doesn't scale and will cause you grief in the long run. Cut your losses while you can.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

The Dixie Flatline (10/5/2010)
Second Steve's sentiments about changing the structure of the table. Whether it's called an EAV or a MUCK, that design has been "invented" again and again over the years by people thinking it's a way to make a relational database more "flexible" or more object-oriented. The design doesn't scale and will cause you grief in the long run. Cut your losses while you can.

BWAA-HAAA!!! C'mon now... you can fit everything into a single table and you only have one index to worry about. I can't understand why all databases aren't single table EAV's. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

nadarajan_v (10/5/2010)
I have a sql server table with the following data:
PrimaryKey ForeignKey DataType Value
1 1 Actor abc
2 1 Movie efg
3 1 Movie hij
4 2 Actor mno
5 2 Movie pqr
6 2 Movie stu
7 2 Movie vwx
I want to select the data from this table and load a Csharp class, named Actors. The class has a property called ActorName and a collection of movies by the actor. Essentially I have to populate the ActorName with data in the Value column and collect all the movie names by the actor from the Value column and populat the collection. I am not sure how to do that, especially how to write the appropriate sql select sattement.
Thanks

I see nothing in this table to necessarily join an actor to a movie. Does the foreigh key supposedly do that? In other words, will all the movies an actor show up in have the same value as the for key column of the actor?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 7

Unfortunately the choice of EAV was made by someone else, higher up. I could only curse him, but somehow make it to work 😉 Based upon a reply from another forum, I fixed it as follows:

SELECT a.Value as ActorName, b.Value as MovieName

FROM (SELECT ForeignKey, Value FROM MyTable WHERE DataType='Actor') a

LEFT OUTER JOIN (SELECT ForeignKey, Value FROM MyTable WHERE DataType='Movie') b

ON a.ForeignKey = b.ForeignKey

ORDER BY a.Value, b.Value

Thanks

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 8

Of course with SQL 2008 you can change the two inline sub-selects to CTEs for pure readability.

That would also get the effect of almost creating two tables, as various other luminaries of this forum have suggested.

WITH

Actors AS (

SELECT

ForeignKey

, Value AS ActorName

FROM MyTable

WHERE DataType='Actor'

)

, Movies AS (

SELECT

ForeignKey

, Value AS MovieName

FROM MyTable

WHERE DataType='Movie'

)

SELECT

ActorName

, MovieName

FROM Actor

LEFT OUTER JOIN Movie

ON Actor.ForeignKey = Movie.ForeignKey