Use Results from a Cross Join for dynamic SQL

Question

Post reply

Use Results from a Cross Join for dynamic SQL

Viewing 15 posts - 16 through 30 (of 43 total)

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

AKShah1 SSC-Addicted Points: 404 More actions · Answer 1

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 is
A 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

David Burrows SSC Guru Points: 65142 More actions · Answer 2

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

SET @CT = @CT + 1

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

SET @CT = @CT + 1

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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 3

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
SET @CT = @CT + 1
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
SET @CT = @CT + 1
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

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 5

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 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

Edited by - AKshah1 on 05/07/2003 07:51:49 AM

David Burrows SSC Guru Points: 65142 More actions · Answer 6

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

SET @CT = @CT + 1

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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 7

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 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
SET @CT = @CT + 1
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

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 8

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

141

242

321

442

543

642

731

I'll leave manipulating these results to find the best combo as an exercise for the reader 🙂

RD Francis

R David Francis

David Burrows SSC Guru Points: 65142 More actions · Answer 9

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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 10

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.

David Burrows SSC Guru Points: 65142 More actions · Answer 11

Have to look again tomorrow, but can u tell me what results you would expect from

1 A Yes

1 B Yes

1 C Yes

2 B Yes

2 C Yes

3 A 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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 12

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 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?

Edited by - AKshah1 on 05/07/2003 10:35:44 AM

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 13

This sounds more and more like the shopping basket analysis (OLAP).

Old example of beer and nappies.

David Burrows SSC Guru Points: 65142 More actions · Answer 14

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.

AKShah1 SSC-Addicted Points: 404 More actions · Answer 15

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!!