sql server - How to Sum Each Enum Value in a Different Column and Don't Repeat Person's Name? -

let's have table sellerid, datetime, producttype register each time a seller sells product. producttype enum type {0=phone, 1=recharge, 2=simcard}. have table sellerid , sellername.

i want make stored procedure shows view:

  id | name | phones | recharges | simcards | total  ----|------|--------|-----------|----------|-------   05 | eddy |      6 |         7 |        0 |    13 

i know how join 2 tables id , name in view, not know how sum each producttype different column. , wanted 1 row each id/name.

can show me how achieve in simple example?

here answer using dynamic pivot. i've created 3rd table on fly called #prodtype contains enum values... entirely optional, makes easier maintain, add table add column... can see added "cases" , "screens" product 3/4 respectively.

/* if temp tables exist, drop them */ if object_id('tempdb..#seller') not null drop table #seller if object_id('tempdb..#prodtype') not null drop table #prodtype if object_id('tempdb..#data') not null drop table #data  /* setup test tables */ create table #seller (sellerid int, name nvarchar(50)) create table #data (sellerid int, saledate datetime, producttype int)  insert #seller (sellerid, name)  values (5, 'eddy'), (6,'dave')  insert #data (sellerid, saledate, producttype) values (5, getdate(), 0),(5, getdate(), 0),(5, getdate(), 0),(5, getdate(), 0),(5, getdate(), 0),(5, getdate(), 0),        (5, getdate(), 1),(5, getdate(), 1),(5, getdate(), 1),(5, getdate(), 1),(5, getdate(), 1),(5, getdate(), 1),(5, getdate(), 1),        (6, getdate(), 0)  /* maintain table enum values*/ create table #prodtype (producttype int, name nvarchar(50)) insert #prodtype (producttype, name) values (0, 'phone'),         (1, 'recharge'),         (2, 'simcard'),         (3, 'case'),         (4, 'screen')  /* main query */           declare @cols nvarchar(max),          @sql  nvarchar(max)  set @cols = stuff( (select n',' + quotename(y) [text()] (select distinct name y #prodtype) y order y xml path('')), 1, 1, n'');          set @sql = 'select sellerid, name, '+@cols+', '+replace(@cols,',','+')+' total             (select s.sellerid, s.name, p.name ptype                   #data d                    join #seller s on d.sellerid=s.sellerid                   join #prodtype p on d.producttype = p.producttype) sub             pivot (count([ptype]) ptype in ('+@cols+')) p' print @sql exec (@sql)