1
Data:
        QID  Type DOB
        -------------
        1    1    01/01/1980
        1    2    03/01/1981
        1    2    01/02/1991
        1    1    01/01/1980
        1    2    03/01/1981
        1    3    01/02/1991
        1    1    01/01/1980
        2    2    03/01/1981
        2    2    01/02/1991
        2    1    01/01/1980
        2    2    03/01/1981
        2    2    01/02/1991

I need to select query that get this result:

    QID  Type(1) Type(2) Type(3)
    ----------------------------
     1     3        3       1
     2     1        4       0

I should use Function or can embedded in select query?

1

You can use the PIVOT operator, like this:

SELECT
   QID, ISNULL([1],0) AS [Type(1)], ISNULL([2],0) AS [Type(2)], ISNULL([3],0) AS [Type(3)]
FROM
   (SELECT QID, Type, COUNT(*) AS row_count
   FROM MyTable
   GROUP BY QID, Type) AS t
PIVOT
   (SUM([row_count]) FOR Type IN ([1],[2],[3])) AS PivotTable;

BOL article about using PIVOT and UNPIVOT.

SQL Fiddle with demo

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.