Need a Creative Sql Query :: HELP

Question

I have a table, a simple table, only has 3 fields.

ID Type Date

Example of Data (recordset showing 3 of 300)

154 | page | 2010-02-08

154 | link | 2010-02-08

154 | form | 2010-02-08

Id: just an id for a client

Type: can only be 3 things that are already populated ('form','page','link')

Date: just the date it was created

There are multiple entries for each ID.

I want to create a stored procedure or query that gives me the following output

Example of Desired OutPut / Count

ID | Link | Form | Page

154 | 75 | 40 | 100

I just want a count of each type so I don't have to make 3 separate SQL calls to get the count of each. Any help would be greatly appreciated, this will need to go to a Stored Procedure, and all I want to base this on is the ID.

Thanks in advance

Answer 1

You might want something like this

Select id
, Sum(Case when Type='Link' then 1 else 0 end) as Links
, Sum(Case when Type='Forms' then 1 else 0 end) as Forms
, Sum(Case when Type='Page' then 1 else 0 end) as Pages
From SomeTable
Group by ID

Answer 2

Give this a try (sql server)

SELECT
    [ID]
        ,SUM( CASE TYPE WHEN 'LINK' THEN 1 ELSE 0 END ) AS [Link]
        ,SUM( CASE TYPE WHEN 'FORMS' THEN 1 ELSE 0 END ) AS [FORMS]
        ,SUM( CASE TYPE WHEN 'PAGE' THEN 1 ELSE 0 END ) AS [Page]
    FROM yourTable
    GROUP BY ID       --remove this line if you are passing in a single @GivenID
    WHERE ID=@GivenID --remove this line if you want counts for all IDs (returns many rows)

Answer 3

A simple PIVOT query should solve the problem

SELECT *
FROM SimpleTable 
PIVOT
(
    COUNT(Date)
    FOR Type IN ([Link], [Form], [Page])
) AS t