Question: I want to get count of 3 columns values combination by each combination in SQL Server. How do I get this?

Ex.

```css
Row    P1   P2   P3
 1     3    10   20
 2     4    15   29
 3     8    10   16
 4     15    4   29
 5     10   20    3

Output Should be:
3  10  20  - 2
4  15  29  - 2
8  10  16  - 1
```

Thanks,

Answer: 
### option 1 -

### cross apply, for xml path, group by.

```
select      vals,count (*) as cnt
from        t cross apply (select ' ' + cast (p as varchar(10)) from (values (p1),(p2),(p3)) as t(p) order by p for xml path('')) v (vals)
group by    vals

```

---

```
vals        cnt
 3 10 20    2
 4 15 29    2
 8 10 16    1

```

### option 2 -

### xquery, group by

```
select      vals
           ,count (*) as cnt

from       (select      cast ((cast ('' as xml)).query('for $i in (sql:column("p1"),sql:column("p2"),sql:column("p3")) order by $i return $i') as varchar(max)) as vals

            from        t
            ) t

group by    vals
;

```

---

```
vals        cnt
-------     ---
3 10 20     2
4 15 29     2
8 10 16     1

```

---

### option 3 -

### Unpivot, row\_number, Pivot.

```
select      [1],[2],[3],count (*) as cnt
from       (select      row,p,row_number () over (partition by row order by p) as n
            from        t unpivot (p for col in (p1,p2,p3)) upv
            ) t pivot (max(p) for n in ([1],[2],[3])) pv
group by    [1],[2],[3]

```

---

```
1   2   3   cnt
--- --- --- ---
3   10  20  2
4   15  29  2
8   10  16  1

```

### option 4 -

### offset n rows, group by.

```
select      [1],[2],[3],count(*) as cnt

from       (select      (select p from (values (p1),(p2),(p3))as t(p) order by p offset 0 rows fetch first 1 row only)  as [1]
                       ,(select p from (values (p1),(p2),(p3))as t(p) order by p offset 1 rows fetch first 1 row only)  as [2]
                       ,(select p from (values (p1),(p2),(p3))as t(p) order by p offset 2 rows fetch first 1 row only)  as [3]

            from        t
            ) t

group by    [1],[2],[3]

```

---

```
1   2   3   cnt
--- --- --- ---
3   10  20  2
4   15  29  2
8   10  16  1

```
This is another approach where in using a `case` expression the least of the 3 values is assigned c1, the second least c2 and the greatest c3. Thereafter,it is just a grouping operation.

```
select c1,c2,c3,count(*) 
from (
select case when p1<=p2 and p1<=p3 then p1
        when p2<=p1 and p2<=p3 then p2
        when p3<=p1 and p3<=p2 then p3 end c1,
   case when p1 between p2 and p3 or p1 between p3 and p2 then p1
        when p2 between p1 and p3 or p2 between p3 and p1 then p2
        when p3 between p1 and p2 or p3 between p2 and p1 then p3 end c2,
   case when p2<=p1 and p3<=p1 then p1
        when p1<=p2 and p3<=p2 then p2
        when p1<=p3 and p2<=p3 then p3 end c3
from t
) x
group by c1,c2,c3

```

**[`Sample Demo`](http://rextester.com/VHYN15508)**

The assumption here is that none of p1,p2,p3 values is null. If they can be `null` , use `coalesce` in the case expression depending on how you want to treat those rows for counting.