January 16, 2012 at 3:04 pm
Need some help in creating a query that will give me a report of items size and color that are stored in a separate table but size and color are in the same table. So, here goes and hopefully you can follow along.
I have two tables (one for the product information and another that has product attributes) I will simpliy the tables.
Table 1 (Product table)
id Name Attributes
1 Product1-M 106,103
2 Product1-L 106,104
3 Product1-XL 106,105
4 Product2-M 107,103
5 Product2-L 107,104
Table 2 (Product Attributes table)
id Attribute ParentID
101 Size 0
102 Color 0
103 Medium 101
104 Large 101
105 X-Large 101
106 Black 102
107 Red 102
So, in table 1 the first product is a black shirt in size large. The attribute field in table one is one field separated with commas.
What I want to do is create a query that will list
product color size
Product1-M Black Medium
Product1-L Black Large
etc..
How can I write a query to produce the output I want to show one column for size and another column for color. Also, table 2 is not alway 101 for size and 102 for color. It has many attributes so I can't just say - get attributes where parentid = 101. Table 2 is a large table that has many attribute for different products. The attributes field in table 1 contains the size and color but I don't know which one is which. I have to use the parentid in table 2 to figure out which attribute is size and which one is color.
Hopefully I haven't confused anyone.
January 16, 2012 at 4:11 pm
Oh man, I really hate these types of designs. Retrieving this type of information should be pretty easy but because of the way it's structured it ends up being more difficult than it needs to be. I don't mean to offend, it's possible this is someone elses handy work and you're coming behind them, just shows how important a good design is though.
Anyways, I have to leave here in a couple minutes so I don't have any working code to show you. I'm thinking you're going to have to split that attribute column on the products table though and join those results to your attribute table. Is there always two attributes in the products table separated by a comma? Is there ever more than 2? Could just split them with charindex and then join that result to the attribute table. There may be a way to do it with a recursive CTE or Jeff Moden's splitter. I will take a crack at this when I get home later tonight.
In the mean time, here's some easily consumable data if someone else wants to give it a go before I get a chance later on.
if object_id('TempDb..#Products') is not null drop table #Products
if object_id('TempDb..#Attribute') is not null drop table #Attribute
create table #Products(ID int, ProductName varchar(20), Attribute varchar(10))
insert into #Products(ID, ProductName, Attribute)
select 1, 'Product1-M', '106,103' union all
select 2, 'Product1-L', '106,104' union all
select 3, 'Product1-XL', '106,105' union all
select 4, 'Product2-M', '107,103' union all
select 5, 'Product2-L', '107,104'
create table #Attribute (ID int, Attribute varchar(10), ParentID int)
insert into #Attribute (ID, Attribute, ParentID)select 101, 'Size', 0 union all
select 102, 'Color', 0 union all
select 103, 'Medium', 101 union all
select 104, 'Large', 101 union all
select 105, 'X-Large', 101 union all
select 106, 'Black', 102 union all
select 107, 'Red', 102
January 16, 2012 at 6:48 pm
I'll say it upfront, the code below works to an extent but it's not good. It will break if you have more than 2 attributes separated by a comma, or if you some rows with one attribute and no comma. It probably won't perform all that well either.
There's a better solution out there though. I'll see if I can come up with one some time tomorrow when I get a chance. Let me know how this works.
if object_id('TempDb..#Products') is not null drop table #Products
if object_id('TempDb..#Attribute') is not null drop table #Attribute
--Creating working tables
create table #Products(ID int, ProductName varchar(50), Attribute varchar(10))
create table #Attribute (ID int, Attribute varchar(10), ParentID int)
--Insert your test data
insert into #Products(ID, ProductName, Attribute)
select 1, 'Product1-M', '106,103' union all
select 2, 'Product1-L', '106,104' union all
select 3, 'Product1-XL', '106,105' union all
select 4, 'Product2-M', '107,103' union all
select 5, 'Product2-L', '107,104'
insert into #Attribute (ID, Attribute, ParentID)
select 101, 'Size', 0 union all
select 102, 'Color', 0 union all
select 103, 'Medium', 101 union all
select 104, 'Large', 101 union all
select 105, 'X-Large', 101 union all
select 106, 'Black', 102 union all
select 107, 'Red', 102
--Here's the query
--Nothing to be proud of, but it works....kind of ;)
select p.ProductName,a.Attribute as 'Color',a2.Attribute as 'Size'
from #products p
inner join (select at.ID, at.Attribute
from #Attribute at
inner join #Attribute at1 on at.ParentID = at1.ID
where at1.Attribute = 'Color') a on a.ID = left(p.Attribute,charindex(',',p.Attribute)-1)
inner join (select at.ID, At.Attribute
from #Attribute at
inner join #Attribute at1 on at.ParentID = at1.ID
where at1.Attribute = 'Size') a2 on a2.ID = right(p.Attribute,(len(p.Attribute)-(charindex(',',p.Attribute))))
January 17, 2012 at 8:04 am
Thanks for responding. Yes, this is inherited and I didn't create the table. I am working on a product feed and need to have the size and color in the feed. I will experiement with your code but there are some attributes that have more than 2 in them. Some may have 3 or 4 separated by a comma for size, color, length, type (whether it is short sleeve or long sleeve), etc..
I just need to find the color and size in the attributes.
Thanks.
January 17, 2012 at 8:10 am
As pointed out this type of structure can be painful to deal with. If possible normalizing this into another table is preferred. That of course is not always an option. So along comes Jeff Moden's splitter. Take a look at the link in my signature about splitting strings. It will enlighten on the journey he took to find the cleanest and fastest approach to parsing/splitting. It will also have the code for his uber simple to use splitter.
This select will parse your string return each element on a new row.
select *
from #Products p
cross apply dbo.DelimitedSplit8K(p.Attribute, ',') s
join #Attribute a on a.id = s.Item
To then get this in the format you desire you will either have to leave the formatting to the front end (recommended) OR build a dynamic cross tab. If you MUST get this data out of sql already formatted do a search for dynamic cross tab and you will find another article by Jeff Moden on that very topic. It is NOT easy to build and will take you quite a bit of effort. If however, you can leave the formatting to the front end the above query should get you what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply