How to select a two dimensional data from sql server

I have a sql server table with the following data:

PrimaryKey  ForeignKey   DataType   Value 
1           1            Actor      abc
2           1            Movie      efg
3        开发者_JS百科   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

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

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