name/value pairs in a table, need help with reporting techniques

name/value pairs in a table, need help with reporting techniques

Post by Ed Lac » Fri, 05 Apr 2002 04:24:37

Hi, I was wondering if someone could lend some experience or
assistance to this problem.

I have a table.

stuff_id int,
stuff_name varchar(10),
stuff_propname varchar(10),
stuff_propvalue varchar(10)

There could be multiple rows for any given "stuff_name" that
correspond to the number of name/value pairs inserted into the table.
I'd like to be able to "turn the row on its side", basically be able
to (through either a view or stored procedure, which ever would be
faster) create a report that would set all of the "stuff_propname"
fields as columns, with the "stuff_propalue"'s displayed in the
corresponding row for that column.

some sample data to make this more clear:

stuff_id stuff_name stuff_propname  stuff_propvalue
-------- ---------- -------------- -----------------
1         apple       color           red
2         apple       taste           yummy
3         apple       origin          new york state
4         tomato      color           red
5         tomato      taste           hearty
6         tomato      origin          new jersey
7         tomato      seeds           true

report would look something like this

stuff_name  color  taste   origin          seeds
----------  -----  -----   ------          -----
apple       red    yummy   new york state  null
tomato      red    heary   new jersey      true

thanks for any and all help,


name/value pairs in a table, need help with reporting techniques

Post by Trys » Sat, 06 Apr 2002 03:50:12


If you know in advance the number of columns in the result set, try:;EN-US;q175574.

If you have a variable number of columns, try:

If you want to cut thru all the sql stuff and go direct use:
Relational Application Companion:


1. Extracting name value pairs from an XML string

I am having a difficult time createing a stored procedure that extracts name
value pairs from an XML string.

I need to extract both element and attribute values.  Here is an example of
the XML that is passed to the stored proc:

EXEC spCLASS_SaveHPAssets '<homepage realtorid="14042222">
  <homepagetab menutabid="1">Home</homepagetab>
  <homepagetab menutabid="20"></homepagetab>
  <homepagedetail attributeid="3" attributename="agadd1">3522 Oak
  <homepagedetail attributeid="5"
  <homepagedetail attributeid="6"
  <homepagedetail attributeid="7"


I need to be able to grab the following values:

homepagetypeid, 12
agadd, 3522 Oak Street

I seem to be able to get attribute values but not the element values.  For
example, the following gets all the "attributename" values but not the
associated "homepagedetail" values:

 With (


Does anyone have some sample code that illustrates how I can access both
type of values?


2. Virtual sqlagent service logged in event viewer

3. PL/SQL Code: Parsing Name Value Pairs.

4. querying from the web

5. Issue with SPs returning name-value pairs


7. writing binary/hexa value to a file

8. Modelling items with attribute name/value pairs

9. How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

10. Table names in dynamic SQL - choice of technique?

11. need temp table technique

12. Indexing technique needed for large table lookup