TSQL: Simple Pivot, Distinct Cells. Can't get it working

The Issue:

I have a table of two columns: Books's and Topics, and a single Topic can have multiple Books referenced to it, and vice verse.

I am trying to count the distinct number of books occurring, and apply them to the pivot. The following code structure is what I have so far:

With dataSource 
as (
select book_id, topic_id
FROM BKINFO.BookTopics
    )
select 
[CS] as 'CmpSci'
,[PGM] + [NET] + [VB] as 'CmpPgm'
,[DB] as 'DB'
,[SQL]+[MYSQL]+[ORA]+[SSRV] as 'SQL'
, [XML]as 'XML'
, [SCI] as 'Science'
, [POE] + [FCT] as 'Lit'开发者_开发技巧
from dataSource
pivot(
 count(book_id)
for topic_id 
in([CS],[PGM],[NET],[VB],[DB],[SQL],[MYSQL],[ORA],[SSRV], [XML],
[SCI],  [POE], [FCT])
)tblPivot

The pivot statements and other selections are there to meet the requirements of the question. (This is for an online course) The data output should appear as follows:

CmpSci      CmpPgm      DB          SQL         XML         Science     Lit
----------- ----------- ----------- ----------- ----------- ----------- -----------
0           28          9           40          2           10          3

What am I missing here? i've spent about 4 hours trying to figure this out and it seems far too simple.

Thanks!

---

Ok, so if the order of the topic in wich the book will be counted doesn't matter, the solution it's actually very simple, you just need to do a MIN or MAX over the topic_id when you define your Datasource table. So, it will be like this:

With dataSource 
as (
select book_id, MIN(topic_id) AS topic_id -- it can be MAX(topic_id) as well
FROM BKINFO.BookTopics
GROUP BY book_id
    )
select 
[CS] as 'CmpSci'
,[PGM] + [NET] + [VB] as 'CmpPgm'
,[DB] as 'DB'
,[SQL]+[MYSQL]+[ORA]+[SSRV] as 'SQL'
, [XML]as 'XML'
, [SCI] as 'Science'
, [POE] + [FCT] as 'Lit'
from dataSource
pivot(
 count(book_id)
for topic_id 
in([CS],[PGM],[NET],[VB],[DB],[SQL],[MYSQL],[ORA],[SSRV], [XML],
[SCI],  [POE], [FCT])
)tblPivot

You should be aware that the book is being counted only in the min (or max) topic it appears. Now, if you want the book to be counted on the topic following a specific order, then I recommend you to create a table topic, with an id int column in the order you like (in this case CS would be id 1, PGM id 2, etc), and in the datasource table calculate the MIN over that column.