How to select a two dimensional data from sql server

Question

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 populate the collection. I am not sure how to do that, especially how to write the appropriate sql select statement.

Thanks

Answer 1

As others pointed out, you need to refactor your DB. If this is not possible, you can issue the following SQL Statement, which uses a self-join:

SELECT actors.Value AS Actor, movies.Value AS Movie 
FROM TableName actors, TableName movies 
WHERE actors.DataType='Actor' 
AND   movies.DataType='Movie'
AND   actors.ForeignKey = movies.ForeignKey

This will return something like

Actor    Movie
abc      efg
abc      hij
mno      pqr
mno      stu
etcetera

Answer 2

Make some SQL Tables.

1- Movies (MovieID, Movie_Name)

2- Actors (ActorId, MovieId, Actor_Name)

These two tables should have relationship. Actors.MovidId should be connected to Movies.MovieId.

I suggest you to use LinQ.

var retVal = (from s in dataContext.Movies select s).ToList();

retVal returns you Movies table which means you got MovieId, Movie_Name

and then you can do anything with this list. Check here 101 LinQ Samples