Efficient SQL procedure to get data from name value pair table into DataSet?

Question

Using SQL Server, have a name value pair table. Each row is basically userid, contentid, sectionid, parameter, value. So there is data I want to display in a table such as user information. Each bit of information is in it's own row, sow how do I get it into a DataSet for use in a Repeater? Can I somehow merge the rows into one? So I can get multiple parameter/value pairs on one row?

so like...

two rows for user 32:

(param / value)
fname / Bob
lname / Smith

displayed on one row in a repeater like this:

Bob Smith

Any ideas? Oh yeah and the reason it is in the name/value pair format is to adhere to a required standard.

Answer 1

Sucky standard.... :)

Anyway, your best bet is to play some magic (cursor) with your stored proc and return the data from the stored procedure in the format you want. Then binding to a DataSet, or a string list, is trivial.

Answer 2

Maybe something like...

SELECT fff.firstname, lll.lastname
FROM (
  SELECT ff.value AS firstname
  FROM PairTable AS ff
  WHERE ff.param = 'fname'
    AND ff.userId = 32
) fff, (
  SELECT ll.value AS lastname
  FROM PairTable AS ll
  WHERE ll.param = 'lname'
    AND ll.userId = 32
) lll

Answer 3

Another alternative is PIVOT.

Something like this (untested because I have no SQL Server around now)

SELECT UserID, [fname] AS firstname, [lname] AS lastname
FROM 
(SELECT UserID, value, name from PairTable WHERE UserID=32 ) p
PIVOT
(
value
FOR name IN
( [fname], [lname] )
) AS pvt

Answer 4

That table is awful. No offense ;-)

Relational databases just doen't do EAV tables very well.

You can also group, and do conditional CASE statements - like this:

SELECT   UserID,
         MAX(CASE WHEN param = 'fname' THEN value ELSE '' END) AS fname,
         MAX(CASE WHEN param = 'lname' THEN value ELSE '' END) AS lname
FROM     MyEAVTable
GROUP BY UserID

The PIVOT syntax is great - the only benefit to this solution is that it will work with SQL 2000 as well.