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?

Improve this question
0

1 Answer 1

Reset to default
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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.