Use Results from a Cross Join for dynamic SQL
-
Hi,
A unique identifier is not a problem, I left it out to keep things simple.
Also ABC would be 4 or more cause AC is already 4 (remmember the relationship is an 'OR').
All combinations need to appear, so the 0 or null is fine
quote:
Thanks that information is helpfull. Can you give me more like on the Products, please? Do each have a unique PK number? And so the Expected output isA 0
B 1
C 3
AB 1
AC 4
ABC 0
BC 0
Or 0 for each combination where not used, right?
Edited by - AKshah1 on 05/07/2003 05:35:46 AM
Edited by - AKshah1 on 05/07/2003 05:38:41 AM
-
How about this for a wacky & crazy solution
create table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
insert into #tblResponses values (5, 'A', 'Yes')
insert into #tblResponses values (5, 'B', 'Yes')
insert into #tblResponses values (5, 'C', 'Yes')
insert into #tblResponses values (6, 'A', 'Yes')
insert into #tblResponses values (6, 'B', 'Yes')
insert into #tblResponses values (6, 'C', 'Yes')
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod SELECT DISTINCT ProdName FROM #tblResponses ORDER BY ProdName
CREATE TABLE #resp (Resp int,Combi varchar(100))
INSERT INTO #resp SELECT DISTINCT Resp,'' FROM #tblResponses ORDER BY Resp
DECLARE @ProdCT int,@CT int
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
SET @CT = 1
WHILE @CT <= @ProdCT
BEGIN
UPDATE r SET r.Combi = r.Combi + x.ProdName FROM #resp r
INNER JOIN #tblResponses x ON x.Resp = r.Resp AND x.Value = 'Yes'
INNER JOIN #prod p ON p.ProdName = x.ProdName AND p.ProdID = @CT
END
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
SELECT a.Combi,SUM(CASE WHEN r.Resp IS NULL THEN 0 ELSE 1 END) AS 'Count'
FROM #AllProd a
LEFT OUTER JOIN #resp r ON r.Combi = a.Combi
GROUP BY a.Combi
ORDER BY a.Combi
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
Clearly your solution does not work!!
Again as I pointed out earlier if ABC has 2 then ABCD should have more then 2 not 0
Regards
quote:
How about this for a wacky & crazy solution
create table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
insert into #tblResponses values (5, 'A', 'Yes')
insert into #tblResponses values (5, 'B', 'Yes')
insert into #tblResponses values (5, 'C', 'Yes')
insert into #tblResponses values (6, 'A', 'Yes')
insert into #tblResponses values (6, 'B', 'Yes')
insert into #tblResponses values (6, 'C', 'Yes')
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod SELECT DISTINCT ProdName FROM #tblResponses ORDER BY ProdName
CREATE TABLE #resp (Resp int,Combi varchar(100))
INSERT INTO #resp SELECT DISTINCT Resp,'' FROM #tblResponses ORDER BY Resp
DECLARE @ProdCT int,@CT int
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
SET @CT = 1
WHILE @CT <= @ProdCT
BEGIN
UPDATE r SET r.Combi = r.Combi + x.ProdName FROM #resp r
INNER JOIN #tblResponses x ON x.Resp = r.Resp AND x.Value = 'Yes'
INNER JOIN #prod p ON p.ProdName = x.ProdName AND p.ProdID = @CT
END
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
SELECT a.Combi,SUM(CASE WHEN r.Resp IS NULL THEN 0 ELSE 1 END) AS 'Count'
FROM #AllProd a
LEFT OUTER JOIN #resp r ON r.Combi = a.Combi
GROUP BY a.Combi
ORDER BY a.Combi
-
Are you saying
1 A Yes
1 B Yes
1 C Yes
produces
A 1
AB 1
AC 1
BC 1
and
1 A Yes
1 B Yes
1 C No
produces
A 1
AB 1
Far away is close at hand in the images of elsewhere.
Anon. -
Yes, I think thats right for one respondent. I am saying
1 A Yes
1 B Yes
1 C Yes
Might produce 1 A or 1B or 1 C if 1 respondent has answered 1 to all the products.
However with if we have 2 respondents and resp1 give yes to A and B. And Resp 2 gives Yes 2 only A & C
then A=2,B=1, and C=1
quote:
Are you saying1 A Yes
1 B Yes
1 C Yes
produces
A 1
AB 1
AC 1
BC 1
and
1 A Yes
1 B Yes
1 C No
produces
A 1
AB 1
Edited by - AKshah1 on 05/07/2003 07:51:49 AM
-
Is this what u want
create table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
insert into #tblResponses values (5, 'A', 'Yes')
insert into #tblResponses values (5, 'B', 'Yes')
insert into #tblResponses values (5, 'C', 'Yes')
insert into #tblResponses values (6, 'A', 'Yes')
insert into #tblResponses values (6, 'B', 'Yes')
insert into #tblResponses values (6, 'C', 'Yes')
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod VALUES ('A')
INSERT INTO #prod VALUES ('B')
INSERT INTO #prod VALUES ('C')
INSERT INTO #prod VALUES ('D')
INSERT INTO #prod VALUES ('E')
INSERT INTO #prod VALUES ('F')
INSERT INTO #prod VALUES ('G')
INSERT INTO #prod VALUES ('H')
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
DECLARE @ProdCT int,@CT int
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
SELECT a.Combi,SUM(CASE WHEN r.ProdName IS NULL THEN 0 ELSE 1 END)
FROM #AllProd a
LEFT OUTER JOIN #tblResponses r ON CHARINDEX(r.ProdName,a.Combi)>0 AND r.Value = 'Yes'
GROUP BY a.Combi
ORDER BY a.Combi
Far away is close at hand in the images of elsewhere.
Anon. -
Hey, not quite there yet, but not bad.
If you have only 6 respondents you cant have a base size of more then 6. In your case you are getting 14...
Basicly if respondent 1 has mentioned A then we dont count him for B when looking at AB he only gets counted once. Again as I said before it's in 'OR' type relationship.
Nice try
Just a thought maybe look at having a distinct count of respondent id's towards the last part of the solution.
The following change to your code works nice:
SELECT a.Combi,COUNT(DISTINCT RESP),SUM(CASE WHEN r.ProdName IS NULL THEN 0 ELSE 1 END)
FROM AllProd a
LEFT OUTER JOIN tblResponses r ON CHARINDEX(r.ProdName,a.Combi)>0 AND r.Value = 'Yes'
GROUP BY a.Combi
ORDER BY a.Combi
One slight problem, this solution will only work for A-Z what about AA etc?
We could have more than 50 Products to look at.
quote:
Is this what u wantcreate table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
insert into #tblResponses values (5, 'A', 'Yes')
insert into #tblResponses values (5, 'B', 'Yes')
insert into #tblResponses values (5, 'C', 'Yes')
insert into #tblResponses values (6, 'A', 'Yes')
insert into #tblResponses values (6, 'B', 'Yes')
insert into #tblResponses values (6, 'C', 'Yes')
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod VALUES ('A')
INSERT INTO #prod VALUES ('B')
INSERT INTO #prod VALUES ('C')
INSERT INTO #prod VALUES ('D')
INSERT INTO #prod VALUES ('E')
INSERT INTO #prod VALUES ('F')
INSERT INTO #prod VALUES ('G')
INSERT INTO #prod VALUES ('H')
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
DECLARE @ProdCT int,@CT int
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
SELECT a.Combi,SUM(CASE WHEN r.ProdName IS NULL THEN 0 ELSE 1 END)
FROM #AllProd a
LEFT OUTER JOIN #tblResponses r ON CHARINDEX(r.ProdName,a.Combi)>0 AND r.Value = 'Yes'
GROUP BY a.Combi
ORDER BY a.Combi
Edited by - AKshah1 on 05/07/2003 08:47:07 AM
Edited by - AKshah1 on 05/07/2003 08:57:51 AM
Edited by - AKshah1 on 05/07/2003 09:08:57 AM
-
OK, here's my idea. I am still using a cursor to define the product combos. However, once defined, the combo list only needs to be changed if products are added or dropped.
First, assume the products are in #tmp_prod, and that the prod column will identify them in the response table.
----------
create table #prod_combo
(combo_id int, prod char(1))
go
declare @counter int
declare @cur_prod char(1)
declare @max_id int
set @counter = 1
declare c1 cursor for select prod from #tmp_prod
open c1
fetch c1 into @cur_prod
if (@@FETCH_STATUS = 0)
BEGIN
insert into #prod_combo VALUES (1, @cur_prod)
fetch c1 into @cur_prod
END
while (@@FETCH_STATUS = 0)
begin
select @max_id = MAX(combo_id) from #prod_combo
insert into #prod_combo select combo_id + @max_id, @cur_prod from #prod_combo
UNION select combo_id + @max_id, prod from #prod_combo
UNION select (2 * @max_id) + 1, @cur_prod
fetch c1 into @cur_prod
end
close c1
deallocate c1
----------
We create a flat table to hold all the possible product combinations. Each combination has a unique ID, and one row for each product in that combination.
It makes life a little easier to have a table containing the distinct combo IDs:
----------
select distinct combo_id into #tmp_combo_ids from #prod_combo
----------
Once we have this, processing the responses becomes easy. Given the following response table:
----------
create table #tmp_resp
(resp_id int, prod char(1), yn char(3))
go
insert into #tmp_resp
select 1, 'A', 'Yes'
UNION
select 1, 'B', 'No'
UNION
select 1, 'C', 'Yes'
UNION
select 2, 'A', 'Yes'
UNION
select 2, 'B', 'Yes'
UNION
select 2, 'C', 'No'
UNION
select 3, 'A', 'Yes'
UNION
select 3, 'B', 'No'
UNION
select 3, 'C', 'Yes'
UNION
select 4, 'A', 'Yes'
UNION
select 4, 'B', 'Yes'
UNION
select 4, 'C', 'Yes'
----------
we can process the responses with the following statement:
----------
select m.combo_id, count(distinct r.resp_id) AS "Would Buy", (select count(*) from #prod_combo where combo_id = m.combo_id) as "Products in Combo"
from #tmp_combo_ids m, #tmp_resp r
where r.yn = 'Yes'
and r.prod in (select prod from #prod_combo where combo_id = m.combo_id)
group by m.combo_id
----------
which yields:
combo_id Would Buy Products in Combo
1 4 1
2 4 2
3 2 1
4 4 2
5 4 3
6 4 2
7 3 1
I'll leave manipulating these results to find the best combo as an exercise for the reader 🙂
RD Francis
R David Francis -
Having trouble with the logic here.
If respondent has A & B then you expect
A 1
If respondent has A & C then you expect
A 1
If respondent has B & C then you expect
B 1
If so you will never get AB combination
Sorry if I seem thick here but I am trying to get a handle on this.
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
I posted a quick response to your previous solution where we could use Distict counts of respondents, recall if a respondent responds more than once to a combination we count them only once. Your previous solution with the small amendment works quite well but does not solve the problem where we have 50 + products, got any ideas?
quote:
Having trouble with the logic here.If respondent has A & B then you expect
A 1
If respondent has A & C then you expect
A 1
If respondent has B & C then you expect
B 1
If so you will never get AB combination
Sorry if I seem thick here but I am trying to get a handle on this.
-
Have to look again tomorrow, but can u tell me what results you would expect from
1 A Yes
1 A Yes
1 A Yes
1 B Yes
1 B Yes
1 C Yes
2 B Yes
2 B Yes
2 B Yes
2 C Yes
2 C Yes
3 A Yes
3 D Yes
3 D Yes
3 D Yes
As for the 50 products, we are using single char codes (A,B etc), are the real products 1 char?
If so, in my solution I allowed for combinations upto 100 single char.
Are the products in a separate table?
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
May be I did not make my self clear,
each respondent will only respond to a unique attribute.
Therefore respondent 1 can only say yes to product A once.
Therefore you could look at the table in the follwowing way:
Resp A B C
1 Y N Y
2 N Y N
Any idea how long it would take to run this?
I guess a lot quicker then using em cursors, at the mo I think you deserve the free book, but lets see if there is anything more elegant.
we would not have a situation where we have the same respondent giving multiple responses.
Regards
quote:
Have to look again tomorrow, but can u tell me what results you would expect from1 A Yes
1 A Yes
1 A Yes
1 B Yes
1 B Yes
1 C Yes
2 B Yes
2 B Yes
2 B Yes
2 C Yes
2 C Yes
3 A Yes
3 D Yes
3 D Yes
3 D Yes
As for the 50 products, we are using single char codes (A,B etc), are the real products 1 char?
If so, in my solution I allowed for combinations upto 100 single char.
Are the products in a separate table?
Edited by - AKshah1 on 05/07/2003 10:35:44 AM
-
This sounds more and more like the shopping basket analysis (OLAP).
Old example of beer and nappies.
-
Ah! Problem is me, me thinks. Sometimes it's difficult to explain what u have and what u want to someone else but I think we are getting there. I agree that a cursor could be a solution but I am trying to keep to set based as much as possible to satisfy the purists (albeit with loops).
Since we are so close can u clarify things a bit further. Based on your last post if the data is
1 A Y
1 B N
1 C Y
2 A N
2 B Y
2 C N
What exactly should the output be.
As to how long. Depends on volume. You said you have 50+ products, can u indicate how many responses there are. What is the definition of product, if you have 50+ they cannot be 1 char!!
Far away is close at hand in the images of elsewhere.
Anon. -
Th ouput for this Would be as follows:
A 1
B 1
C 1
AB 2
AC 1
BC 2
ABC 2
Answer the rest shortly
quote:
Ah! Problem is me, me thinks. Sometimes it's difficult to explain what u have and what u want to someone else but I think we are getting there. I agree that a cursor could be a solution but I am trying to keep to set based as much as possible to satisfy the purists (albeit with loops).Since we are so close can u clarify things a bit further. Based on your last post if the data is
1 A Y
1 B N
1 C Y
2 A N
2 B Y
2 C N
What exactly should the output be.
As to how long. Depends on volume. You said you have 50+ products, can u indicate how many responses there are. What is the definition of product, if you have 50+ they cannot be 1 char!!
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply