T-SQL Column values count

问题:

Say I have a table A and it has 5 columns (Column1, Column2.. Column5), the values in each column is one char size and stored only as alphabetic as follows

    ID Column1 Column2 Column3 Column4 Column5
    1   A        C       D        A      B
    2   A        D       A        B      A
    3   B        K       Q        C      Q
    4   A        K       E        E      B
    5   F        K       F        F      S

I need a count of each different value stored in column1 to column5, I want the following information

     Column1 has A's count=3, B's count=1, F's count=1
     Column2 has C's count=1, D's count=1, K's count=3

     and so on

What is the correct way and format to return these values?

Thanks

回答1:

You could try:

SELECT 'Col1' As 'Col', Column1 as 'Value', COUNT(*) as 'Ct'
FROM MyTable
GROUP BY Column1
UNION ALL
SELECT 'Col2', Column2, COUNT(*)
FROM MyTable
GROUP BY Column2
...

You will need to write an additional SELECT to UNION for each column you want to aggregate, but it will return the data you are after.

回答2:

Can you just execute an individual query for each needed column using a GROUP BY?

SELECT Column1, COUNT(Column1) FROM TableName
GROUP BY Column1

回答3:

You can use unpivot in a derived table (or CTE) and group by column name and value in the outer query.

Try this:

declare @T table
(
  ID int,
  Column1 char(1), 
  Column2 char(1), 
  Column3 char(1),
  Column4 char(1),
  Column5 char(1)
)

insert into @T values
(1,   'A',        'C',       'D',        'A',      'B'),
(2,   'A',        'D',       'A',        'B',      'A'),
(3,   'B',        'K',       'Q',        'C',      'Q'),
(4,   'A',        'K',       'E',        'E',      'B'),
(5,   'F',        'K',       'F',        'F',      'S')

;with C as
(
  select ID, Col, Val
  from (
         select ID, Column1, Column2, Column3, Column4, Column5
         from @T
       ) as T
  unpivot (Val for Col in (Column1, Column2, Column3, Column4, Column5)) as U   
)
select Col, Val, count(*) as ValCount
from C
group by Col, Val
order by Col

回答4:

The union approach is going to server you best. The individual query for each union would look something like this:

Select Distinct Column1, COUNT(Column1)OVER(PARTITION BY Column1) Col1Count, 'Column1' ColumnName
From ColTable
Union All
...