Complex query help

Question

Complex query help

SSC Journeyman

Points: 87

May 22, 2006 at 10:32 am

I have two tables, tbl_ProdAttr (Product attributes) and tbl_Attributes. I am trying to return a table form result with the tbl_Attribure.headertext across the top with corresponding values in the columns.

tbl_ProdAttr.AttributeID = tbl_Attributes.AttributeID

An example of what I am trying to achieve.

ProdNo.

Attribute.HeaderText

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

ProdAttr.PartNo

ProdAttr.Value

Is this possible to achieve or am I missing something?

Thank you.

Chris Green

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

It looks like you're asking for a pivot/crosstab query. You can search this site for that to find several related questions.

I find this (or something based on it) to be handy for this kind of situation...

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

If you post your table structure, some sample data and the result you'd like for that sample data, you'll no doubt get someone to write the query for you, or at least get you closer.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

greencg SSC Journeyman Points: 87 More actions · Answer 2

Thank you Ryan for your reply...

The table structure as you recommended

tbl_ProdAttr

ProductNum PK varchar(20)

AttributeID PK int

Value varchar(20)

tbl_Attribute

AttributeID PK int

HeaderText Varchar(50)

ArtributeId

HeaderText

1

Size 1

2

Size 2

3

List Price

4

Min Order

ProductNum

AttributeID

Value

1111

1

½

1111

2

7/8

1111

3

4.25

1111

4

36

ProductNum

Size1

Size2

List Price

Min Order

1111

½

7/8

4.25

36

I will check out the link -- the thing that throws me off is I am not doing any calculations and everything I see for crosstabs/pivot tables calls for calculations!

thanks again

Chris

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Hi Chris,

The calculations thing shouldn't throw you off - most of the examples are not really about calculations, that's probably just the max keyword trick you're seeing.

Here's how you can 'hard code' your example. The stored procedures you see which do this basically construct something like this using dynamic sql, and then execute that. That's the only way you can get 'dynamic columns'.

Hope this helps

--data

declare @tbl_Attribute table (AttributeID int, HeaderText Varchar(50))

insert @tbl_Attribute

select 1, 'Size 1'

union all select 2, 'Size 2'

union all select 3, 'List Price'

union all select 4, 'Min Order'

declare @tbl_ProdAttr table (ProductNum varchar(20), AttributeID int, Value varchar(20))

insert @tbl_ProdAttr

select 1111, 1, '1/2'

union all select 1111, 2, '7/8'

union all select 1111, 3, '4.25'

union all select 1111, 4, '36'

--calculation

select

ProductNum,

max(case when HeaderText = 'Size 1' then Value else null end) as 'Size 1',

max(case when HeaderText = 'Size 2' then Value else null end) as 'Size 2',

max(case when HeaderText = 'List Price' then Value else null end) as 'List Price',

max(case when HeaderText = 'Min Order' then Value else null end) as 'Min Order'

from @tbl_ProdAttr p inner join @tbl_Attribute a on a.AttributeID = p.AttributeID

group by ProductNum

--result

ProductNum Size 1 Size 2 List Price Min Order

-------------------- -------------------- -------------------- -------------------- --------------------

1111 1/2 7/8 4.25 36

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

greencg SSC Journeyman Points: 87 More actions · Answer 4

Ryan,

Thank you again for replying to my post...

Would it be efficient to user a cursor to determine the values I would use to genereate the headers?

I have about 3500 different products and alot of them have different attributes. I am building a web catalog and need to build that table dynamically. Some tables will have 3 columns and others may have 8.

That is my challenge.

I hope I am making some sense...

Thanks,

Chris

RyanRandall SSChampion Points: 13623 More actions · Answer 5

You shouldn't need to reinvent the wheel. Use something like this (and modifiy slightly, if necessary)...

http://www.sqlteam.com/item.asp?ItemID=2955

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

greencg SSC Journeyman Points: 87 More actions · Answer 6

Thanks for pointing me to that link...

I was able to figure it out.

Got what I needed...

Thanks

RyanRandall SSChampion Points: 13623 More actions · Answer 7

Great! Thanks for the feedback

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.