Hello Everyone,

I am looking for a query which can give me a count of combination of different columns of a table.

Here is the DDL and DML

use [HealthChoice]

CREATE TABLE [dbo].[Ingredients](
	[ID] int Identity(1,1),
	[V1] varchar(10),
	[V2] varchar(10),
	[V3] varchar(10),
	[V4] varchar(10),
	[V5] varchar(10)
) ON [PRIMARY]

GO
--sample data

INSERT INTO [dbo].[Ingredients]
     VALUES
	 ('carrot','beetroot','cabbage','lemon','lettuce'),
	 ('cabbage','lemon','lettuce','okra','pepper'),
	 ('carrot','mushroom','cabbage','olives','lettuce'),
	 ('olives','okra','pepper','lemon','mushroom'),
	 ('carrot','beetroot','cabbage','lemon','lettuce'),
	 ('chilli','brinjal','cabbage','lemon','lettuce'),
	 ('carrot','spinach','cabbage','lemon','lettuce')
GO

Here is the sample output:

Count of Combination of veggies(sample out):

veggies	Count
carrot,beetroot,cabbage,lemon,lettuce	2
carrot,beetroot,cabbage,lemon	2
carrot,beetroot,cabbage	2
carrot,beetroot	2
cabbage,lemon	4

            

• Edited by SQLSPUser Monday, February 23, 2015 10:12 PM

Is this possible?

Try this:

select v1+','+v2+case when v3 = '' then '' else ',' end +coalesce(v3,'')+case when v4 = '' then '' else ',' end +coalesce(v4,'')+case when v5 = '' then '' else ',' end +coalesce(v5,'') veggies, Contar [count] from (
select v1,v2, v3,v4,v5, COUNT(ID) contar from ingredients
group by v1,v2, v3,v4,v5
union
select v1,v2, '' v3,'' v4 ,'' v5, COUNT(ID) contar from ingredients
group by v1,v2
union
select v1,v3, '','','', COUNT(ID) from ingredients
group by v1,v3
union
select v1,v4, '','','', COUNT(ID) from ingredients
group by v1,v4
union
select v1,v5, '','','', COUNT(ID) from ingredients
group by v1,v5
union
select v2,v3, '','','', COUNT(ID) from ingredients
group by v2,v3
union
select v2,v4, '','','', COUNT(ID) from ingredients
group by v2,v4
union
select v2,v5, '','','', COUNT(ID) from ingredients
group by v2,v5
union
select v3,v4, '','','', COUNT(ID) from ingredients
group by v3,v4
union
select v3,v5, '','','', COUNT(ID) from ingredients
group by v3,v5
union
select v4,v5, '','','', COUNT(ID) from ingredients
group by v4,v5
union
select v1,v2, v3,'','', COUNT(ID) from ingredients
group by v1,v2,v3
union
select v1,v2, v4,'','', COUNT(ID) from ingredients
group by v1,v2,v4
union
select v1,v2, v5,'','', COUNT(ID) from ingredients
group by v1,v2,v5
union
select v1,v3, v4,'','', COUNT(ID) from ingredients
group by v1,v3,v4
union
select v1,v3, v5,'','', COUNT(ID) from ingredients
group by v1,v3,v5
union
select v1,v4, v5,'','', COUNT(ID) from ingredients
group by v1,v4,v5
union
select v2,v3, v4,'','', COUNT(ID) from ingredients
group by v2,v3,v4
union
select v2,v3, v5,'','', COUNT(ID) from ingredients
group by v2,v3,v5
union
select v2,v4, v5,'','', COUNT(ID) from ingredients
group by v2,v4,v5
union
select v3,v4, v5,'','', COUNT(ID) from ingredients
group by v3,v4,v5
union
select v1,v2, v3,v4,'', COUNT(ID) from ingredients
group by v1,v2,v3,v4
union
select v1,v2, v3,v5,'', COUNT(ID) from ingredients
group by v1,v2,v3,v5
union
select v1,v2, v4,v5,'', COUNT(ID) from ingredients
group by v1,v2,v4,v5
union
select v1,v3, v4,v5,'', COUNT(ID) from ingredients
group by v1,v3,v4,v5
union
select v2,v3, v4,v5,'', COUNT(ID) from ingredients
group by v2,v3,v4,v5
union
select v1,v2, v3,v4,v5, COUNT(ID) from ingredients
group by v1,v2,v3,v4,v5) AS xxx

Hi SQLSPUser,

To achieve your requirement, you may reference the below query.

;WITH cte AS( --get distinct veggies
 SELECT V1 FROM [Ingredients]
 UNION 
 SELECT V2 FROM [Ingredients]
 UNION 
 SELECT V3 FROM [Ingredients]
 UNION 
 SELECT V4 FROM [Ingredients]
 UNION   
 SELECT V5 FROM [Ingredients]
 ),
 cte2 AS( -- get all permutations
SELECT CAST( v1 AS NVARCHAR(MAX) ) AS nv, 1 AS LEVEL
FROM cte c
UNION ALL
SELECT CAST( c.v1 + ',' + c2.nv AS NVARCHAR(MAX) ) , LEVEL + 1 AS LEVEL
FROM cte2 c2, cte c
WHERE c.v1 > c2.nv 
 ),
cte3 AS( --filter the permutations
SELECT * FROM cte2 WHERE LEVEL BETWEEN 2 AND 5
),
cte4 AS( --get the match relation between veggies(each column) and permutations
SELECT c.nv,
	   CASE WHEN CHARINDEX(igd.v1,c.nv)>0 THEN 1 ELSE 0 END AS V1match,
	   CASE WHEN CHARINDEX(igd.v2,c.nv)>0 THEN 1 ELSE 0 END AS V2match,
	   CASE WHEN CHARINDEX(igd.v3,c.nv)>0 THEN 1 ELSE 0 END AS V3match,
	   CASE WHEN CHARINDEX(igd.v4,c.nv)>0 THEN 1 ELSE 0 END AS V4match,
	   CASE WHEN CHARINDEX(igd.v5,c.nv)>0 THEN 1 ELSE 0 END AS V5match,
	   c.LEVEL
FROM Ingredients igd CROSS JOIN cte3 c
),
cte5 AS ( --get the match relation between each row and the permutations
SELECT * FROM CTE4 WHERE V1match+V2match+V3match+V4match+V5match = LEVEL
)
SELECT nv AS veggies, COUNT(1) AS cnt
FROM cte5 
GROUP BY nv
ORDER BY cnt DESC
;

If you have any feedback on our support, please click

Not giving the expected resultset

Thanks Eric. Works. I will also need to do the same thing for a table with an identity column and 12 int columns and get unique combinations for that as well. Will replacing the column names in above script and adding additional columns do the trick?

Thanks again

• Edited by SQLSPUser 14 hours 24 minutes ago

Here is the DDL and DML

CREATE TABLE [dbo].[DATA1](
[C1]  [int] NULL,
[C2]  [int] NULL,
[C3]  [int] NULL,
[C4]  [int] NULL,
[C5]  [int] NULL,
[C6]  [int] NULL,
[C7]  [int] NULL,
[C8]  [int] NULL,
[C9]  [int] NULL,
[C10] [int] NULL,
[C11] [int] NULL,
[C12] [int] NULL
) ON [PRIMARY]

insert into data1 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)  Values
(1,3,4,5,6,7,9,18,20,22,23,24),
(1,2,3,6,8,13,14,15,16,20,21,22),
(2,4,8,11,12,13,14,16,17,18,23,24),
(1,3,5,6,8,9,10,15,20,21,23,24),
(1,3,4,7,9,10,11,15,20,21,22,23),
(1,4,5,7,11,12,14,17,18,21,22,23),
(5,6,9,10,11,12,13,14,16,18,19,20),
(2,3,7,9,12,13,15,16,17,18,19,24),
(1,4,5,6,7,13,14,19,21,22,23,24),
(1,2,3,4,6,7,11,13,14,18,21,24),
(2,3,4,6,7,8,10,16,19,20,21,22),
(2,4,5,7,9,13,16,17,18,20,22,24),
(3,4,5,7,10,11,12,13,16,18,23,24),
(1,2,3,6,8,11,12,13,15,19,21,22)

Hi SQLSPUser,

Replacing the column names and adding additional columns can do the trick. But pay attention to the complexity of this subsequent case, the permutation and combination volume can be so huge that it would take pretty much time to handle. May I know is this case for some specific purpose or is that just for your curiosity?

If you have any feedback on our support, please click here.