performing a loop in the SELECT clause

  • davidandrews13

    SSCertifiable

    Points: 6689

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396782

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • davidandrews13

    SSCertifiable

    Points: 6689

    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

  • davidandrews13

    SSCertifiable

    Points: 6689

    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)

  • Lynn Pettis

    SSC Guru

    Points: 442365

    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.

  • ChrisM@Work

    SSC Guru

    Points: 186120

    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

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • davidandrews13

    SSCertifiable

    Points: 6689

    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 code that 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.

  • davidandrews13

    SSCertifiable

    Points: 6689

    edit

  • ChrisM@Work

    SSC Guru

    Points: 186120

    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)

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • davidandrews13

    SSCertifiable

    Points: 6689

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

  • ChrisM@Work

    SSC Guru

    Points: 186120

    davidandrews13 (12/4/2009)


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

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

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • davidandrews13

    SSCertifiable

    Points: 6689

    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

  • Madhivanan-208264

    SSCertifiable

    Points: 7516

    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

  • Lynn Pettis

    SSC Guru

    Points: 442365

    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.

  • ChrisM@Work

    SSC Guru

    Points: 186120

    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?

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply