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.

t_stuff
--------
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,
Ed

 
 
 

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

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


Hello,

If you know in advance the number of columns in the result set, try:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574.

If you have a variable number of columns, try:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

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

 
 
 

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">
 <homepagetypeid>12</homepagetypeid>
 <homepagetabs>
  <homepagetab menutabid="1">Home</homepagetab>
  <homepagetab menutabid="20"></homepagetab>
 </homepagetabs>
 <homepagedetails>
  <homepagedetail attributeid="3" attributename="agadd1">3522 Oak
Street</homepagedetail>
  <homepagedetail attributeid="5"
attributename="agcity">Westfield</homepagedetail>
  <homepagedetail attributeid="6"
attributename="agcountry">USA</homepagedetail>
  <homepagedetail attributeid="7"

 </homepagedetails>
</homepage>

I need to be able to grab the following values:

homepagetypeid, 12
agadd, 3522 Oak Street
agcity,Westfield
agcountry,USA

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?

Thanks
Dave

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

6. PLEASE CONFIRM URGENTLY.

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