August 28, 2009 at 12:22 pm
I have a table that contains store IDs and product IDs. For each store there can be multiple product IDs:
Store Product
1 A
1 B
1 C
2 A
2 B
3 A
3 B
3 C
4 A
I want to group the stores where each group would contain common products. So in the example above store 1 and 3 would be in the same group, 3 would be alone in its own group, as would 4. What would be the best way to accomplish this?
Thanks,
Dave
August 28, 2009 at 1:18 pm
can you tell us what you're expecting in a result-set form?
like:
StoreID NumberofTotalProductPerStore
1 24
2 35
3 29
Something similar would help.
Thanks,
S
--
:hehe:
August 28, 2009 at 7:54 pm
declare @myTable table(store int, product char(1))
insert into @myTable
(
store
,product
)
select 1 ,'A' union
select 1 ,'B' union
select 1 ,'C' union
select 2 ,'A' union
select 2 ,'B' union
select 3 ,'A' union
select 3 ,'B' union
select 3 ,'C' union
select 4 ,'A'
-----------------------------
select distinct
store
,rtrim(substring(isnull((select ','+ product from @myTable t2 where t1.store = t2.store for xml path('')),' '),2,2000)) StoreCategory
from @myTable t1
------------------------------
The code is between the hyphens. I just added that above table var to make sure it would work. This will create a category for stores with all of the same product names. This might work for you.
August 30, 2009 at 5:55 am
Christopher Gordon (8/28/2009)
declare @myTable table(store int, product char(1))insert into @myTable
(
store
,product
)
select 1 ,'A' union
select 1 ,'B' union
select 1 ,'C' union
select 2 ,'A' union
select 2 ,'B' union
select 3 ,'A' union
select 3 ,'B' union
select 3 ,'C' union
select 4 ,'A'
-----------------------------
select distinct
store
,rtrim(substring(isnull((select ','+ product from @myTable t2 where t1.store = t2.store for xml path('')),' '),2,2000)) StoreCategory
from @myTable t1
------------------------------
The code is between the hyphens. I just added that above table var to make sure it would work. This will create a category for stores with all of the same product names. This might work for you.
Although the query works and gives the correct results, I would modify it the inner query (the one with the for xml clause) and add an order by clause. The reason for that, is that I’d want to be guaranteed that the order of the products will be the same for each store and that I will never have 2 strings because of the order of the records in the inner query (e.g. one string would look like this ‘A,B,C’ and the other would look like this ‘A,C,B’).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2009 at 9:01 am
Actually what I did was use the CHECKSUM_AGG() function on product Id. I then did a distinct query to get the individual store records. Even though the CHECKSUM_AGG() value changes as updates are made to the table it still allows me to group the stored correctly.
SELECT dbo.CampaignDetail.CampaignID, dbo.CampaignDetail.StoreId, CHECKSUM_AGG(dbo.Lots.ProductId) AS pidCHECKSUM
FROM dbo.CampaignDetail
GROUP BY dbo.CampaignDetail.CampaignID, dbo.CampaignDetail.StoreId
August 30, 2009 at 7:09 pm
Gotcha, makes sense. This function wouldn't work though based on the example you provided though since Checksum_agg() seems to only work on integers. That said, I'll add that aggregate function to my tool box. Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply