Ok, had to use a cursor, try this.
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')
--Create table of unique products
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod SELECT DISTINCT ProdName FROM #tblResponses ORDER BY ProdName
--Count them
DECLARE @ProdCT int,@CT int,@Keys varchar(7000),@Combi varchar(100),@ProdName char(1),@TempKeys varchar(7000)
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
--Create table of all unique product combinations
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
--create table of Yes responses
CREATE TABLE #resp (Resp int,ProdName char(1))
INSERT INTO #resp SELECT Resp,ProdName FROM #tblResponses WHERE Value = 'Yes'
CREATE TABLE #AllResp (Combi varchar(100),Keys varchar(100),KeyCount int)
CREATE TABLE #AllResp2 (Combi varchar(100),Keys varchar(100),KeyCount int)
CREATE TABLE #AllResp3 (Combi varchar(100),Keys varchar(100),KeyCount int)
--create table of products and responses
SET @CT = 1
WHILE @CT <= @ProdCT
BEGIN
SET @Keys=''
SELECT @Keys=@Keys+CONVERT(varchar,r.Resp)+',' FROM #resp r INNER JOIN #prod p ON p.ProdName = r.ProdName AND p.ProdID = @CT
INSERT INTO #AllResp2 SELECT ProdName,@Keys,0 FROM #prod WHERE ProdID = @CT
END
INSERT INTO #AllResp SELECT Combi,Keys,KeyCount FROM #AllResp2
--Add combinations
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllResp3
--Get new combinations
INSERT INTO #AllResp3
SELECT DISTINCT a.Combi+b.ProdName,a.Keys,0
FROM #AllResp2 a
CROSS JOIN #resp b
WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
--for each new combination get responses
DECLARE curs CURSOR FOR SELECT Combi,Keys FROM #AllResp3
OPEN curs
FETCH NEXT FROM curs INTO @Combi,@TempKeys
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Keys=''
SET @ProdName = RIGHT(@Combi,1)
SELECT @Keys=@Keys+CONVERT(varchar,r.Resp)+',' FROM #resp r WHERE r.ProdName = @ProdName AND CHARINDEX(CONVERT(varchar,r.Resp)+',',@TempKeys)=0
--and add to record
UPDATE #AllResp3 SET Keys=Keys+@Keys WHERE CURRENT OF curs
FETCH NEXT FROM curs INTO @Combi,@TempKeys
END
CLOSE curs
DEALLOCATE curs
--put new combinations and responses in table
INSERT INTO #AllResp SELECT Combi,Keys,KeyCount FROM #AllResp3
TRUNCATE TABLE #AllResp2
INSERT INTO #AllResp2 SELECT Combi,Keys,KeyCount FROM #AllResp3
END
--Update combination count
SELECT @CT = COUNT(*) FROM #AllResp WHERE Keys <> ''
WHILE @CT > 0
BEGIN
UPDATE #AllResp SET KeyCount=KeyCount+1,Keys=REPLACE(Keys,LEFT(Keys,CHARINDEX(',',Keys)),'') WHERE Keys <> ''
SELECT @CT = COUNT(*) FROM #AllResp WHERE Keys <> ''
END
--Show list
SELECT a.Combi,ISNULL(r.KeyCount,0) AS 'Count'
FROM #AllProd a
LEFT OUTER JOIN #AllResp r ON r.Combi = a.Combi
ORDER BY LEN(a.Combi),a.Combi
Far away is close at hand in the images of elsewhere.
Anon.