December 3, 2009 at 5:34 am
what i'm trying to do is the following:
select
SUM(CASE WHEN c.categoryID = 1 then 1 else 0 end) as '1',
SUM(CASE WHEN c.categoryID = 2 then 1 else 0 end) as '2',
SUM(CASE WHEN c.categoryID = 3 then 1 else 0 end) as '3',
SUM(CASE WHEN c.categoryID = 4 then 1 else 0 end) as '4',
SUM(CASE WHEN c.categoryID = 5 then 1 else 0 end) as '5'
from
Customer c
INNER JOIN Categories c ON c.categoryID = c.ID
the problem is that new categories can be added by the user when ever they wish.
so me hard coding the c.categoryID = {ID} will very quickly fall apart.
as such, i wish to do a loop which will find all categories within the Categories table and then SUM them together.
is that possible?
here's some test data:
declare @Customers table(ID int,firstname varchar(50),surname varchar(50),categoryID int)
declare @Categories table(ID int,catName varchar(50))
insert into @Customers
select 1,'bob','smith',1
union
select 2,'john','roberts',2
union
select 3,'peter','james',3
insert into @Categories
select 1,'leg'
union
select 2,'arm'
union
select 3,'head'
select
SUM(CASE WHEN c.categoryID = 1 then 1 else 0 end) as '1',
SUM(CASE WHEN c.categoryID = 2 then 1 else 0 end) as '2'
from
@Customers c
INNER JOIN @Categories cat ON c.categoryID = cat.ID
this data is showing that if i hard coded the case statement for categoryIDs 1 and 2, and then the new category (ID 3) was subsequently added, it wouldn't show it.
December 3, 2009 at 6:53 am
I'm probably extremely confused, but wouldn't this do what you're looking for?
SELECT c.categoryID
,SUM(c.categoryID)
FROM @Customers c
INNER JOIN @Categories cat ON c.categoryID = cat.ID
GROUP BY c.categoryID
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2009 at 7:27 am
hi. i'm afraid not 🙂
i should have put more test data in.
i wantto get a count of how many people fall into category 1. and how many people fall into category 2 etc etc.
but i dont know what categories will be present in the @Categories table when my stored procedure is run. there could only be 4 different categories, or there could be 10.
declare @Customers table(ID int,firstname varchar(50),surname varchar(50),categoryID int)
declare @Categories table(ID int,catName varchar(50))
insert into @Customers
select 1,'bob','smith',1
union
select 2,'john','roberts',2
union
select 3,'peter','james',3
union
select 4,'peter','james',2
union
select 5,'paul','thomas',2
union
select 6,'pau','simpson',3
insert into @Categories
select 1,'leg'
union
select 2,'arm'
union
select 3,'head'
select
SUM(CASE WHEN c.categoryID = 1 then 1 else 0 end) as 'No. of Cat 1 issues',
SUM(CASE WHEN c.categoryID = 2 then 1 else 0 end) as 'No. of Cat 2 issues'
from
@Customers c
INNER JOIN @Categories cat ON c.categoryID = cat.ID
December 3, 2009 at 7:41 am
OK. i think i see where your going with this. so i could do it like this?
select * from
(SELECT c.categoryID
,count(c.CategoryID) as catcount
FROM @Customers c
INNER JOIN @Categories cat ON c.categoryID = cat.ID
GROUP BY categoryID)x
pivot
(max(catcount)
for CategoryID in ([1],[2]))y
but then i'm back to the begining again. that is, i'm currently hard coding the categoryID ([1],[2],etc)
December 3, 2009 at 7:51 am
Sounds like a dynamic pivot may be required here. Please chack out the article references in my signature block below regarding Cross Tabs and Pivots.
December 3, 2009 at 8:27 am
Lynn Pettis (12/3/2009)
Sounds like a dynamic pivot may be required here. Please chack out the article references in my signature block below regarding Cross Tabs and Pivots.
Lynn beat me to it, but it will look something like this:
CREATE TABLE #Customers (ID int,firstname varchar(50),surname varchar(50),categoryID int)
CREATE TABLE #Categories (ID int,catName varchar(50))
insert into #Customers
select 1,'bob','smith',1 union
select 2,'john','roberts',2 union
select 3,'peter','james',3 union
select 4,'peter','james',2 union
select 5,'paul','thomas',2 union
select 6,'pau','simpson',3
insert into #Categories
select 1,'leg' union
select 2,'arm' union
select 3,'head'
SET QUOTED_IDENTIFIER OFF
DECLARE @SQL VARCHAR(8000)
SET @SQL = "SELECT "
SELECT @SQL = @SQL +
"MAX(CASE WHEN catname = '" + cat.catName + "' THEN Issues ELSE 0 END) AS [" + cat.catName + "], "
FROM #Categories cat
SET @SQL = LEFT(@SQL, LEN(@SQL)-1) +
"FROM (SELECT cat.ID, cat.catName, COUNT(*) AS Issues
FROM #Customers c
INNER JOIN #Categories cat ON c.categoryID = cat.ID
GROUP BY cat.ID, cat.catName) d"
EXEC (@SQL)
DROP TABLE #Customers
DROP TABLE #Categories
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 3, 2009 at 8:45 am
i'm slowly getting there 🙂
this is part of what i have at the moment
declare acursor cursor for
select id from accidentriddorcategories
open acursor
fetch next from acursor into @catID
while (@@FETCH_STATUS <> -1)
begin
set @CaTs += '[' + convert(varchar(1000),@catID) + '],'
fetch next from acursor into @catID
end
CLOSE aCursor
DEALLOCATE aCursor
set @CaTs = LEFT(@cats,len(@cats)-1)
declare @sql nchar(1000) =
'select * ,
' + convert(varchar(2),@team) + ' AS teamNo,
(SELECT longdes FROM Regions WHERE Active =1 AND TeamNo = ' + convert(varchar(2),@team) + ')AS teamName
from
(SELECT acc.riddorcat
,count(acc.riddorcat) as catcount
FROM accidents acc
INNER JOIN AccidentRiddorCategories cat ON acc.riddorcat = cat.ID
GROUP BY riddorcat)x
pivot
(max(catcount)
for riddorcat in (' + @CaTs + '))y'
exec sp_executesql @sql
unforutnately my sql codethat is filling @sql requires an extra where clause that checks between two dates.
so when i put this next line between the 'inner join' and 'group by' lines, it gives a conversion error 'Conversion failed when converting date and/or time from character string.
where dateofaccident between ' + dateadd(yy,-7,getdate()) + 'and ' + getdate() + '
i'm using the DATEADD and GETDATE() functions just for testing purposes. the real stored procedure will require date parameters sent to it.
December 3, 2009 at 8:50 am
edit
December 3, 2009 at 8:55 am
Replacing the cursor code with this will simplify things for you.
DECLARE @CaTs varchar(1000)
SELECT @CaTs = ISNULL(@cats, '') + '[' + cat.catName + '], '
FROM #Categories cat
-- Display
SELECT [Category list] = LEFT(@cats,len(@cats)-1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 4, 2009 at 6:24 am
thanks everyone. got it to work. 😎
December 4, 2009 at 6:32 am
davidandrews13 (12/4/2009)
thanks everyone. got it to work. 😎
Top work, David. How about posting your solution for the curious?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 4, 2009 at 6:52 am
OK. its a bit difficult for me to give some test data for this because the declaring of the temp tables and filling them would have to go into the @sql variable as well.
and that would render it not looking like it would do in a real life situation.
i haven't had time to have a look at changing the cursor yet as i've been taken off this report and given something else at the moment.
also, the tables and column names in this example aren't the real ones and may differ slightly from the test data i gave earlier on as i attempted to change them fairly quickly.
BEGIN
/*these three will be the parameters that are passed to the stored procedure*/
declare@team int = 6,
@startdate smalldatetime = '2002-12-03',
@EndDate smalldatetime = '2009-12-03'
--i then had to convert the dates to varchar
declare @startdate2 varchar(50) = CONVERT(varchar(50),@startdate,106)
declare @EndDate2 varchar(50) = CONVERT(Varchar(50),@enddate,106)
declare @CaTs varchar(1000) = ''
declare @catID int
declare acursor cursor for
select
id
from
@Categories
open acursor
fetch next from acursor into @catID
while (@@FETCH_STATUS <> -1)
begin
set @CaTs += '[' + convert(varchar(1000),@catID) + '],'
fetch next from acursor into @catID
end
CLOSE aCursor
DEALLOCATE aCursor
set @CaTs = LEFT(@cats,len(@cats)-1)
declare @sql nchar(1000) =
'select * , '
+ convert(varchar(2),@team)
+ ' AS teamNo,
''' + @startdate2 + '''AS startdate,''' + @enddate2 + ''' AS enddate,
(SELECT Name FROM Regions WHERE Active =1 AND TeamNo = ' + convert(varchar(2),@team) + ')AS teamName
from
(SELECT c.categoryID
,count(c.CategoryID) as catcount
FROM
@Customers c
INNER JOIN @Categories cat ON c.CategoryID = cat.ID
WHERE
dateofcustomer between ''' + @startdate2 + ''' and ''' + @enddate2 + '''
GROUP BY CategoryID)x
pivot
(max(catcount)
for CategoryID in (' + @CaTs + '))y'
exec sp_executesql @sql
END
December 4, 2009 at 7:04 am
Another way of doing this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Failing to plan is Planning to fail
December 4, 2009 at 7:21 am
Please, when you get a chance, go back and eliminate the cursor. Since there is a set-based solution, you really should use that as it should scale better than the cursor-based solution.
December 4, 2009 at 7:32 am
davidandrews13 (12/4/2009)
OK. its a bit difficult for me to give some test data for this because the declaring of the temp tables and filling them would have to go into the @sql variable as well.and that would render it not looking like it would do in a real life situation.
What does the output data set look like?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply