How to get column data in column names in query?

Question

If I have the following table:

 Cust Prod     Qty
===== ====     ===
 Bob   Apple    3
 Bob   Orange   2
 Bob   Banana   4
 Rob   Apple    2
 Rob   Orange   1
 Bob   Apple    2

How can I get the following result with the table data as the column names:

Prod   Bob Rob
====== === ===
Apple    5   2
Orange   2   1
Banana   4  null

Answer 1

SELECT * from t
pivot
(
  sum(Qty)
  for [Cust] in ([Bob],[Rob])
)as p;
GROUP BY PROD

fiddle demo

Answer 2

You can use PIVOT in MSSQL or the following way:

SELECT
PROD,
SUM(CASE WHEN Cust='Bob' THEN Qty ELSE 0 END) as Bob,
SUM(CASE WHEN Cust='Rob' THEN Qty ELSE 0 END) as Rob

FROM T
GROUP BY PROD

SQLFiddle demo