SQL Server Pivot Function Understanding

Question

I have a table that looks like this:

Source    TableName     Detail     active_status     RowCount
a          table_one     stuff      active               500
b          table_two     stuff_2    active               750
c          table_three   stuff_3    inactive            1000
d          table_four    stuff_4    active               200
e          table_five    stuff_5    inactive             200

What I want is this result:

result      a     b      c     d    e
result    500   750   1000   200 2000

Here is the SQL Query I am using:

select 'results' as results, ['a'], ['b'], ['c'], ['d'], ['e']
from (select [source], [rowcount] from ParentStaged) as src_tbl
pivot
(
    sum([rowcount])
    for source in (['a'], ['b'], ['c'], ['d'], ['e'])
) as pivot_tbl;

This produces this:

results    a      b    c    d    e
results null   null null null null

What am I doing wrong?

Answer 1

You need to specify column values used as headers without quotes in your pivot,

For example:

select 'results' as results, a, b, c, d, e
from (select [source], rowcnt from ParentStaged) as src_tbl
pivot
(
    sum(rowcnt)
    for source in (a, b, c, d, e)
) as pivot_tbl;

OR

select 'results' as results, [a], [b], [c], [d], [e]
from (select [source], rowcnt from ParentStaged) as src_tbl
pivot
(
    sum(rowcnt)
    for source in ([a], [b], [c], [d], [e])
) as pivot_tbl;