Using Sum() to sum two columns

Transact-SQL
1

I have a table with columns as follows:
table1
member_id course_type course_length
2 C1 4
3 C1 2
2 C2 1
3 C2 3
3 C1 6
2 C1 3

How can I use SUM() to sum the course_length of each course for each member?
Wanna have final table format:
member_id course_length_C1 course_length_C2
2 7 1
3 8 3

2 
use sqlteam
go

create table #shimsham(member_id int, course_type char(10), course_length int)

insert into #shimsham
select 2 , 'C1', 4 union
select 3 , 'C1', 2 union
select 2 , 'C2', 1 union
select 3 , 'C2', 3 union
select 3 , 'C1', 6 union
select 2 , 'C1', 3 


select member_id, [C1], [C2]  from
(
select member_id, course_length, course_type
  From #shimsham
) p
PIVOT 
(
SUM (course_length)
FOR course_type in ([C1], [C2])
) a


drop table #shimsham
3

dynamic way of doing it

use sqlteam
go

declare @Columns nvarchar(max), @query  nvarchar(max)

create table #shimsham(member_id int, course_type varchar(50), course_length int)

insert into #shimsham
select 2 , 'C1', 4 union
select 3 , 'C1', 2 union
select 2 , 'C2', 1 union
select 3 , 'C2', 3 union
select 3 , 'C1', 6 union
select 2 , 'C1', 3 union
select 4, 'Darth Vader', 5

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(course_type)
FROM
(
select distinct course_type 
  from #shimsham

) AS B
ORDER BY B.course_type



SET @query ='
select member_id, ' + @Columns + ' from
(
select member_id, course_length, course_type
  From #shimsham
) p
PIVOT 
(
SUM (course_length)
FOR course_type in (' + @Columns + ')
) a '

exec(@query)

drop table #shimsham
1 Like