Need a Creative Sql Query :: HELP
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 开发者_如何学Cthe 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
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
A simple PIVOT query should solve the problem
SELECT *
FROM SimpleTable
PIVOT
(
COUNT(Date)
FOR Type IN ([Link], [Form], [Page])
) AS t
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)
精彩评论