Have Geek Brain

Efficient SQL procedure to get data from a table of value pairs of values ​​in a DataSet?

Using SQL Server, enter a table of name value pairs. Each line is basically a userid, contentid, sectionid, parameter, value. Therefore, there is data that I want to display in the table, for example user information. Each bit of information is on its own line, sows, how do I get it into a DataSet for use in a relay? Can I somehow concatenate the strings into one? So I can get multiple parameter / value pairs in one line?

so that...

two lines for user 32:

(param / value)
fname / Bob
lname / Smith

displayed on one line in the repeater as follows:

Bob Smith

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

0


a source to share


4 answers


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
+1


a source


Sucky standard .... :)



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

0


a source


Another alternative is PIVOT .

Something like this (untested because I don't have SQL Server right 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
0


a source


This table is terrible. No offense; -)

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

You can also group and execute conditional CASE statements - for example:

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 of this solution is that it will work with SQL 2000 as well.

0


a source






More articles:


All Articles