performing a loop in the SELECT clause

  • 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.

  • 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

  • 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

  • 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)

  • 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 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

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    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

  • 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.

  • edit

  • 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)

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    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

  • thanks everyone. got it to work. ๐Ÿ˜Ž

  • davidandrews13 (12/4/2009)


    thanks everyone. got it to work. ๐Ÿ˜Ž

    Top work, David. How about posting your solution for the curious?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    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

  • 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

  • Another way of doing this

    http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx


    Madhivanan

    Failing to plan is Planning to fail

  • 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.

  • 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?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    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