Get values only when in use - SQL Server Forums

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

Get values only when in use

Author

Topic

Dobly
Starting Member

16 Posts

Posted - 2011-07-27 : 02:46:32

Hi

Thanks to a lack of foresight (NOT my design) I have a few tables with ugly way of defining and storing parameter data.

First is the Attributes table. The Attributes table is a bit like this

AttributeID PK
Parameter1Title varchar(100)
Parameter1InUse bit
Parameter2Title varchar(100)
Parameter2InUse bit
Parameter3Title varchar(100)
Parameter3InUse bit
...

Then there is an AttributeMap Table that looks like this.

AttributeMapID
AttributeID
AttibuteValueID
ItemID ( this is the item to which the attribute is assigned.)

Finally there is the Attribute value table.

AttributeValueID
Parameter1Value
Parameter2Value
Parameter3Value

In a nutshell attributes can be assigned to any number of Items. Each AttributeValue row is a set of data that matches with the Attributes that may or may not be In Use. (man this is doing my head in)

Basically the user can fill in any or all of the Parameters on a given attribute.

Herein lays my problem.

What I need to is return just the Parameters that are In Use for a given AttributeID and ItemID

The returned dataset just needs to contain the fields:

Parameter Value

Where 'Parameter' is one of the ParameterXTitle (attributes table) and the Value is from a matching ParameterXValue (attributeValue table)

For example

Parameter Value
Age 65
Color Blue

Next set might be

Parameter Value
Size Large
Width 34
Height 44

Where
'Age' was Parameter1Title and '65' was in Parameter1Value
'Color' as Parameter3Title and 'Blue' was in Parameter3Value

and so on.

That is, regardless of which Parameter was used, whether it was Parameter2Title or Parameter9Title, it gets returns AS Parameter along with its Value

What makes this hard (for me) is that there is not actual relationship between any ParameterTitle and a particular ParameterValue, other than:

The AttributeID, The ItemID and the name of the columns!

This dataset is getting bound to a Telerik RadGrid that is expecting just the Parameter and Value columns.

In a way I need to look at each set of data within the table, see IF it is in use and if it is, add it to the returned set.

I can't help but think I am going to need a temp table and a CURSOR (the horror) or, redesign the way the app stores attribute parameters (I would love to do this properly ,but a redesign is last resort based on the amount of work this would cause)

Help please!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:15:41

something like:-


SELECT AttributeID,ItemID,Parameter
INTo #Parameters
FROM
(
SELECT a.AttributeID,a.ItemID,
CASE WHEN Parameter1InUse=1 THEN Parameter1Title
ELSE NULL END AS P1Title,
CASE WHEN Parameter2InUse=1 THEN Parameter2Title
ELSE NULL END AS P2Title,
....
FROM AttributeMap am
JOIN Attributes a
ON a.AttributeId = am.AttributeID
JOIN AttributeValue av
ON av.AddtibuteValueID = am.AttibuteValueID
WHERE am.AttributeID = @AttributeID
AND am.ItemID = @ItemID
)t
UNPIVOT(Parameter FOR Title in ([P1Title],[P2Title],...))u

SELECT AttributeID,ItemID,ParamValue
INTO #Param_values
FROM
(
SELECT a.AttributeID,a.ItemID,
CASE WHEN Parameter1InUse=1 THEN Parameter1Value
ELSE NULL END AS P1Value,
CASE WHEN Parameter2InUse=1 THEN Parameter2Value
ELSE NULL END AS P2Value,
....
FROM AttributeMap am
JOIN Attributes a
ON a.AttributeId = am.AttributeID
JOIN AttributeValue av
ON av.AddtibuteValueID = am.AttibuteValueID
WHERE am.AttributeID = @AttributeID
AND am.ItemID = @ItemID
)t
UNPIVOT(ParamValue FOR Title IN ([P1Value],[P2Value],...))u

SELECT p.Parameter,pv.ParamValue
FROM #Parameters p
JOIn #Param_values pv
ON pv.AttributeID = p.AttributeID
AND pv.ItemID = p.ItemID

DROP TABLE #Parameters 
DROP TABLE #Param_values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources