Post reply

Assign ungrouped terms to "Other" either in query or related table?

  • May 20, 2012 at 5:29 pm

    #256150

    I have created a table that will assign several activities in my Primary table into four groups (running = Group 1, jogging = Group 1, walking = Group 2, sleeping = Group 3, etc.) when queried.

    How do I get all other activities not specified in that secondary table to be assigned a group of "Other"? They are too numerous to list, plus new ones are constantly being added. I just want to lump them all into an Other catch-all category.

  • May 20, 2012 at 6:00 pm

    #1490213

    Without table definition(s), some sample data and the desired results when using the sample data, providing you with a tested answer is next to impossible.

    Please, please post your table definition(s), some sample data. To do so is quite easy. Please click on the first link in my signature block and read the article. The article contains the T-SQL code to provide the information / data requested so that some one can assist you with a tested solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 20, 2012 at 9:16 pm

    #1490222

    Perhaps something like this?

    DECLARE @primary TABLE (ID INT IDENTITY, Activity VARCHAR(30))

    DECLARE @types TABLE (Activity VARCHAR(30), ActGroup VARCHAR(5))

    INSERT INTO @primary (Activity)

    SELECT 'Running' UNION ALL SELECT 'Jogging'

    UNION ALL SELECT 'Walking' UNION ALL SELECT 'Sleeping'

    UNION ALL SELECT 'Swimming' UNION ALL SELECT 'Dreaming'

    INSERT INTO @types

    SELECT 'Running', '1'

    UNION ALL SELECT 'Jogging', '1'

    UNION ALL SELECT 'Walking', '2'

    UNION ALL SELECT 'Sleeping', '3'

    SELECT ID, p.Activity

    ,CASE WHEN ActGroup IS NULL THEN 'Other' ELSE ActGroup END As ActGroup

    FROM @primary p

    LEFT OUTER JOIN @types t ON p.Activity = t.Activity

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 3 posts - 1 through 2 (of 2 total)

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