How to select columns when columns change

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to select columns when columns change

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-08-13 : 14:46:27

I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.

I have four tables. Product Line, Item, Property, and Value.

A Product Line has many items and an item can have many property's and a property can have many values.

I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.

Any help would be appreciated.

Thanks,
vmon

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:52:25

can you post some sort of data, better yet include sql to create tables and a few inserts for the tables and then the required result. you can do it for just one product like.

Ashley Rhodes

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-08-13 : 15:26:07

Here is some of the data.

ProductLine ProductLineDescr
Blanks Blanks
ColPosts Columns and Posts Products
DecAcces Decorative Accessories Products
Displays Sales Displays

ProductLine PropertyId
ColPosts Diamenter
ColPosts Length
ColPosts Type
ColPosts Special
ColPosts Group
DecAcces Thickness
DecAcces Width
DecAcces Length
DecAcces Product Type
DecAcces Species
Displays Description
Displays Group

ProductLine PropertyId ValueId
ColPosts Diamenter 6
ColPosts Diamenter 8
ColPosts Diamenter 10
ColPosts Diamenter 12
ColPosts Diamenter -----
ColPosts Length 3072
ColPosts Length 3840
ColPosts Length -----

When I take the above and relate it to an Item in the table below. I want to select a product line like ColPost and get the data below that.

tblItemValue
ItemId
ProductLine
PropertyId
ValueId

I would want to see a result something like this for ColPosts with the ValueId's under their appropriate column.

ItemId Diameter Length Type Special Group

And when I chose DecAces I would want this:

ItemId Thickness Width Length ProductType Species

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources