View--Best way to accomplish

[edmana]

I have 2 tables, tblParts and tblAttributes. Each row in tblAttributes has a column that links to tblParts, an attribute name, and a value. Here is some sample data:

tblParts

ID Desc
1 Small Widget
2 Medium Widget
3 Large Widget

tblAttributes

ID PartID Attribute Desc
1 1 Color Blue
2 1 Weight 1 Pound
3 1 UPC 123456789
4 2 Color Yellow
5 2 Weight 2 Pounds
6 3 Color Red
7 3 UPC 345667788

I need to produce a view that shows the following:


ID Desc Color Weight UPC
1 Small Widget Blue 1 Pound 123456789
2 Medium Widget Yellow 2 Pounds
3 Large Widget Red 345667788

I was able to do this as a view by bringing in aliases of tblAttibutes and setting a WHERE clause to equal the name of the attribute name so I only get returned the colors returned for that alias. I then put it into a column w/ an alias named "Color".

I don't think it's the most efficient as if more attributes are added later, I won't have them available unless I change the view.

Does anyone have any ideas?

Thanks!
Ed

 

[Qik3Coder]

This type of data presentation is normally referred to a as a CROSSTAB.
The skinny answer is no, this is the pain of normalizing data like that.

faq183-5269
faq183-5278

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[bborissov]

SELECT tblParts.ID,
       tblParts.Desc,
       MAX(CASE WHEN tblAttributes.Attribute = 'Color' 
                     THEN  tblAttributes.Desc END) AS Color,
       MAX(CASE WHEN tblAttributes.Attribute = 'Weight' 
                     THEN  tblAttributes.Desc END) AS Weight,
       MAX(CASE WHEN tblAttributes.Attribute = 'UPC' 
                     THEN  tblAttributes.Desc END) AS UPC
FROM tblParts
INNER JOIN tblAttributes ON tblParts.Id = tblAttributes.PartId
GROUP BY tblParts.ID,
         tblParts.Desc

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[tkaz]

I need help with my SQL. I have written a simple select statement that returns the data I need for 6 different periods based on specific date ranges. I need to combine it all for the purpose of trending YTD ASP for the 6 periods.

I need to know that at this location...

000011 YTD asp has been 10.84, 10.72, 10.47, 10.45, 10.40, 10.28

and graph it by Period in a line chart.

I have included the code below that I have that does what I want, but I need it all together. Also, is there a better way to work with the dates than hard coding?

If you can lead me in the right direction, I would appreciate it.



select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [1]
from MSSA
where Mth >= '2010-05-01'
and Mth <= '2011-04-01'
and Loc in ('00011','00013','00021','00041','00043','00070'
group by Loc

select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [2]
from MSSA
where Mth >= '2010-04-01'
and Mth <= '2011-03-01'
and Loc in ('00011','00013','00021','00041','00043','00070')
group by Loc

select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [3]
from MSSA
where Mth >= '2010-03-01'
and Mth <= '2011-02-01'
and Loc in ('00011','00013','00021','00041','00043','00070')
group by Loc

select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [4]
from MSSA
where Mth >= '2010-02-01'
and Mth <= '2011-01-01'
and Loc in ('00011','00013','00021','00041','00043','00070')
group by Loc

select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [5]
from MSSA
where Mth >= '2010-01-01'
and Mth <= '2010-12-01'
and Loc in ('00011','00013','00021','00041','00043','00070')
group by Loc

select Loc,SUM(MatlTotal) Dollars, SUM(MatlUnits) Units, (SUM(MatlTotal)/ SUM(MatlUnits)) as [6]
from MSSA
where Mth >= '2009-12-01'
and Mth <= '2010-11-01'
and Loc in ('00011','00013','00021','00041','00043','00070')
group by Loc

 

[gmmastros]

I need it all together

Do you mean that you want one recordset that has all the data? Or do you mean that you want one row that has a bunch of columns?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SajidAttar]

Which version of SQL Server you are using?

Did you give a try to Pivot operator?

By "I need it all together" if you mean to have all six resultset / recordssets together in one single resultset, then you can use Union All.

M2C...

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD