sql - How to get count values for multiple values in one column -

sql - How to get count values for multiple values in one column -

my info this

name values val1 val1 val2 val2 val2 b val1 b val2

i want ouput info way

name val1count val2count 2 3 b 1 1

i can name , count(*) val1 query.

select [name],count(*) [table1] [values]='val1' grouping [name]

but not sure how count(*) val2 in same row.

i tried doing this, looks not supported

select [name],@val1count= (above query val1), @val2count = (above query val2)

please help. looking.

this called pivoting. databases provide pivot function. however, can manually.

select [name], sum ( case when [values]='val1' 1 else 0 end ) val1count, sum ( case when [values]='val2' 1 else 0 end ) val2count [table1] grouping [name]

explanation:

the case when ... end gives each row "boolean" value whether or not row matches condition. the sum ( ... ) counts number of rows returned "true" (or 1). the group [name] consolidates rows downwards 1 row per distinct name. if add together conditions where clause, case when see rows matching where conditions.

sql