1

I have a table of data and need to extract count of value on report table on excel, but i need to show any rows and columns in my report.

My table looks like:

ID  Val  Sets
1   aa   25
2   aa   26
3   bb   25
4   cc   27
5   aa   27
6   aa   25

And my report in this format:

    25  26  27
aa  2   1   1
bb  1   0   0
cc  0   0   1
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
Hadi Ranji
  • 203
  • 1
  • 2
  • 11

1 Answers1

2

With conditional aggregation:

select val,
       sum(case when sets = 25 then 1 else 0 end) as [25],
       sum(case when sets = 26 then 1 else 0 end) as [26],
       sum(case when sets = 27 then 1 else 0 end) as [27]
from tablename
group by val

With pivoting:

select val,
       [25],
       [26],
       [27]
from tablename
pivot(count(id) for sets in([25],[26],[27]))p
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • thanks , that's work but only for 25,26,27 i have a lot of column ! how can use this query for many Sets ? – Hadi Ranji Jul 07 '15 at 17:50
  • Look for dynamic pivoting. There are plenty of similar questions here. For example http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Giorgi Nakeuri Jul 07 '15 at 17:55