You have a table containing the following data:

Table1

Type	ProductID
X	1
X	2
Y	1
A	1
A	2
A	3
A	4

Create a query to output the sums by type like this:

Count(X)	Count(Y)	Count(A)
2	1	4

 

ANSWER:

If you know which values you have in the column type, you can use the
following query:

SELECT [A], [X], [Y]
FROM (SELECT [type] FROM Table1) AS D
  PIVOT(COUNT([type]) FOR [type] IN([A], [X], [Y])) AS P;

Otherwise, you will need to query the distinct type values and construct the
query dynamically:

DECLARE @cols AS NVARCHAR(500), @sql AS NVARCHAR(600);

SET @cols =
  STUFF(
    (SELECT N',' + QUOTENAME([type]) AS [text()]
     FROM (SELECT DISTINCT [type] FROM Table1) AS D
     ORDER BY [type]
     FOR XML PATH('')), 1, 1, N'');

SET @sql = N'SELECT ' + @cols + N'
FROM (SELECT [type] FROM Table1) AS D
  PIVOT(COUNT([type]) FOR [type] IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;