Use Results from a Cross Join for dynamic SQL
-
I have an intresting problem, basicly I am using a cross join to get a list of all product combinations :. if we have 3 products we get 8 posibilities.
To get this just add one table to SQL server, with one cols ProdName(char) then add 3 records 'a','b' and 'c'.
Now create a cross join adding the same table 3 times.
The resulting view gives the combinations.
My problem is to use the results from this cross join view so that I can query another table :
Resp Prod Value
1 A 5
1 B 4
1 C 5
2 A 3
2 B 5
2 C 4
etc.. for 1000 respondents
At the moment I am thinking of using a cursor for to go through the rows resulting from the cross join and the creating a sql statment dynamicly to query the respondents data table. I am sure there is a more efficient solution.
Please help, or come up with other option.
Regards
-
Readers, this looks interesting - so let's make it interesting! I've got a copy of a book on ADO.Net or MDX for the person with the 'best' response that works! Best covers a lot of ground, bonus points for original/elegant/unusual solutions.
Andy
-
Ok, I will throw this out for anyone who wants to try the 2nd half. I named my ProdName containing table prodx with the ProdName column as the only item. Here is the Join to generate the unique possibilities (there are 10 by the way).
select
x1.ProdName x1,
x2.ProdName x2,
x3.ProdName x3
from
prodx x1
cross join
prodx x2
cross join
prodx x3
where
x2.ProdName NOT IN (
(case when x1.ProdName in (select top 2 ProdName from prodx order by ProdName desc) then (select top 1 ProdName from prodx order by ProdName asc) else '' end),
(case when x1.ProdName = (select top 1 ProdName from prodx order by ProdName desc) then (select top 1 ProdName FROM (select top 2 ProdName from prodx order by ProdName asc) as x ORDER BY ProdName desc) else '' end)
)
AND
x3.ProdName NOT IN (
(case when x1.ProdName in (select top 2 ProdName from prodx order by ProdName desc) or x2.ProdName in (select top 2 ProdName from prodx order by ProdName desc) then (select top 1 ProdName from prodx order by ProdName asc) else '' end),
(case when (select top 1 ProdName from prodx order by ProdName desc) in (x1.ProdName,x2.ProdName) then (select top 1 ProdName FROM (select top 2 ProdName from prodx order by ProdName asc) as x ORDER BY ProdName desc) else '' end)
) -
now based on my understanding of the problem you have a view that is something of the type
col1 col2 col3
a b c
a c b i.e the product combinations...
and then you have the responses table with the response and the product and the value...
and you want to query one with the other based on the product combination from the view with the product combination from the responses table...
I hope I am on track so far...
*takes deep breath*...and here is what I have come up with...
first get the values from the responses table into a format that can easily be compared to the product combinations...then do whatever comparision is needed...
Select Resp,Prod1,Prod1Value,Prod2,Prod2Value,Prod3,Prod3Value
from
(
select Resp,
CASE AValue WHEN AValue THEN 'a' END Prod1,AValue As Prod1Value,
CASE BValue WHEN BValue THEN 'b' END Prod2,BValue As Prod2Value,
CASE CValue WHEN CValue THEN 'c' END Prod3,CValue As Prod3Value from
(
select Resp,
SUM(CASE Prod WHEN 'a' THEN Value ELSE 0 END) as AValue,
SUM(CASE Prod WHEN 'b' THEN Value ELSE 0 END) as BValue,
SUM(CASE Prod WHEN 'c' THEN Value ELSE 0 END) as CValue
from Resp
Group by Resp
)As InnerTab
)As OuterTab
and now that Prod1,Prod2,Prod3 are the combination of the products in the responses table these can be compared with the product combinations in the view adding this where clause to the query written above :
where Prod1+'#'+Prod2+'#'+Prod3 in
(select Col1+'#'+Col2+'#'+Col3 from ViewName)
In case I have gone way off course (which I am beginning to suspect might be the case) then can I be nominated for wierdest response...??
-
You know. Thinking about this now I realize, what are you wanting the result to be for the final output and how are you visioning getting it? I am not sure of the final product.
-
I assumed it would be something like "find all respondents for a particular product combination" or "find total value for a particular product combination" and similar things...
unfortunately not the first time I've written queries based on "my" understanding of the problem...
-
Try 1, not generic enough
Set NoCount on
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TResp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TResp]
GO
CREATE TABLE [dbo].[TResp] ([Resp] [char] (1) NOT NULL ,[ProdName] [char] (1) NOT NULL ,[Value] [int] NOT NULL)
GO
Insert TResp Values('1','A',1)
Insert TResp Values('1','B',2)
Insert TResp Values('1','C',3)
Insert TResp Values('2','A',4)
Insert TResp Values('2','B',5)
Insert TResp Values('2','C',6)
Insert TResp Values('3','A',7)
--Insert TResp Values('3','B',8)
Insert TResp Values('3','C',9)
gO
Declare @P1 char(1),
@P2 char(1),
@P3 char(1)
Select @p1='A',
@P2='B',
@p3='C'
Select m1.Resp,
@p1 as Product1,m1.Value as Product1Value,
@p2 as Product2,m2.Value as Product2Value,
@p3 as Product3,m3.Value as Product3Value
From
TResp as m1
Inner Join TResp as m2
On m1.Resp=m2.Resp And
m1.ProdName=@p1 And
m2.ProdName=@p2
Inner Join TResp as m3
On m3.Resp=m1.Resp And
m3.ProdName=@p3
Set NoCount Off
-
Can I ask some qualifying q's
How do you get 8 combinations from 3 products? If you want unique combinations then there are only 6
select a.ProdName,b.ProdName,c.ProdName
from prodx a
cross join prodx b
cross join prodx c
where (a.ProdName <> b.ProdName) and (b.ProdName <> c.ProdName) and (a.ProdName <> c.ProdName)
order by a.ProdName,b.ProdName,c.ProdName
will give you
ProdName ProdName ProdName
-------- -------- --------
a b c
a c b
b a c
b c a
c a b
c b a
what is required for the first part?
Antares, your solution (when I ran it) gave 10 combinations!!
How will the first query/view be used with the respondents table? There is no indication of the relationship or the required output!!!
Can someone help me out here or am I being a dumb @$$.
Far away is close at hand in the images of elsewhere.
Anon. -
Right DavidBurrows, I think we need more pieces to this puzzle. He stated all product combinations with 3 CROSS JOINS which actually gives far more than 8. All possible unique combinations would be
aaa
aab
abb
abc
aac
acc
bbb
bbc
bcc
ccc
Unless your statement is what he is after
abc
acb
bac
bca
cba
cab
But then the question is how does it relate to his other table
Resp Prod Value
1 A 5
1 B 4
1 C 5
2 A 3
2 B 5
2 C 4
etc.. for 1000 respondents
as there is no information to go on we really need to see what he is expecting as opposed to making assumptions.
AKShah1 if you would explain in more detail
1) What do you mean by all possible combinations for A,B,C?
2) Provide a better sampling of what to expect in the data.
3) Explain the sample data to us.
4) Create a sample output you would expect and how you came about that.
Without that information we really are not doing anything but guessing.
-
and no prizes for guessing....
*couldn't resist that*
-
First of all thanks for the high response on this problem. Let me clarify a number of issues that have cropped up.
1) The 8 combinations are:
We can ignore the null combinations :. 7
ABC
ABC
ANullC
ABNull
ANullNull
NullBC
NullNullC
NullBNull
2) The final output, well I should have stated this in beginning here goes:
Hypothetical scenario (simplified)
We have a situation where we have a number of respondents stating their preferences for a particular product lets say 3 products for now.
Each respondent shall give a preference for which product(s) they would use.
Now we want to find out which is the best combination of products to market with the lowest cost. We would be interested in finding out which combinations of products give the best coverage in the market.
e.g. ProdA ProdB ProdC
Resp1 Yes No Yes
resp2 Yes Yes No
Resp3 No No Yes
Resp4 Yes Yes No
In the above for a one product only situation you would get best coverage by selecting product A, however for a 2 product solution we would be better with product B and C
The output would be a list of all combinations and the number of respondents. Note that the condition is an 'OR'.
:.
Combination Resp
A 3
B 2
C 2
AB 3
AC 4
BC 4
ABC 4
We can ignore the null combinations.
The more complex problem comes when responses are not just a yes or no, but a preference such as Definitely would buy, Might buy, not buy etc..
Hope this makes things a little more clear.
-
Just a note about the data format. I am open to the format I thought the following was best:
RespIdProdValue
1AYes
1BNo
1CYes
2AYes
2BYes
3CNo
etc..
Basicly storing multiple records for each respondent. The other option would be
Resp ProdAProdBProdC
1YesNoYes
2YesYesNo
etc..
The first format is better cause we dont know how many products could come through. Also we dont want a table with over 30 columns
hope it helps.
-
Since you are ignoring NULLS then this I think is what you are looking for is something like this.
SELECT
(CASE WHEN ProdA = 1 THEN 'A' ELSE '' END) +
(CASE WHEN ProdB = 1 THEN 'B' ELSE '' END) +
(CASE WHEN ProdC = 1 THEN 'C' ELSE '' END) AS Combination,
COUNT(*) as Resp
FROM
(
SELECT
Resp,
SUM(CASE WHEN ProdName = 'A' AND Value = 'Yes' Then 1 Else 0 END) AS ProdA,
SUM(CASE WHEN ProdName = 'B' AND Value = 'Yes' Then 1 Else 0 END) AS ProdB,
SUM(CASE WHEN ProdName = 'C' AND Value = 'Yes' Then 1 Else 0 END) AS ProdC
FROM
tblResponses
GROUP BY
Resp
) AS tblPivot
GROUP BY
ProdA,
ProdB,
ProdCEdited by - antares686 on 05/07/2003 04:58:11 AM
-
quote:
Since you are ignoring NULLS then this I think is what you are looking for is something like this.
SELECT
(CASE WHEN ProdA = 1 THEN 'A' ELSE '' END) +
(CASE WHEN ProdB = 1 THEN 'B' ELSE '' END) +
(CASE WHEN ProdC = 1 THEN 'C' ELSE '' END) AS Combination,
COUNT(*) as Resp
FROM
(
SELECT
Resp,
SUM(CASE WHEN ProdName = 'A' AND Value = 'Yes' Then 1 Else 0 END) AS ProdA,
SUM(CASE WHEN ProdName = 'B' AND Value = 'Yes' Then 1 Else 0 END) AS ProdB,
SUM(CASE WHEN ProdName = 'C' AND Value = 'Yes' Then 1 Else 0 END) AS ProdC
FROM
tblResponses
GROUP BY
Resp
) AS tblPivot
GROUP BY
ProdA,
ProdB,
ProdCEdited by - antares686 on 05/07/2003 04:58:11 AM
This does not solve the problem. It does not provide results for all the combinations.
Also note that the 3 products is just an example we would have about 30 products.
For most cross-tabulation type problems a really good tool is the RAC (Replacment for Access Cross tabulation) stored procedures, if that helps.
Regards
Edited by - AKshah1 on 05/07/2003 05:24:12 AM
Edited by - AKshah1 on 05/07/2003 05:27:28 AM
-
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?
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply