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

```
Oh, this is a pain.

```
select p_1, p_2, p_3, count(*) as cnt
from t cross apply
     (select max(case when seqnum = 1 then p end) as p_1,
             max(case when seqnum = 2 then p end) as p_2,
             max(case when seqnum = 3 then p end) as p_3
      from (select p, row_number() over (order by p) as seqnum
            from (values (p1), (p2), (p3)
                 ) v(p)
           ) rp
     ) rp
group by p_1, p_2, p_3;

```

What is this doing? The `cross apply` first unpivots the values onto separate rows. Then it enumerates the values (using `row_number()`, ordering by the value. This generates a sequence number, which is used for pivoting the values -- in order.

Finally, with the values in order on each row, we can aggregate the values to get the counts you are asking for.