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

SET @CT = @CT + 1

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

SET @CT = @CT + 1

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

SET @CT = @CT + 1

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