AdventureWorks Database: Change the display of product line categories with comment

SQL Query - AdventureWorks: Exercise-128 with Solution

128. From the following table write a query in SQL to change the display of product line categories to make them more understandable. Return ProductNumber, category, and name of the product. Sort the result set in ascending order on ProductNumber.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Product table and applying a CASE statement to categorize products by ProductLine
SELECT   
    -- Selecting the ProductNumber column
    ProductNumber,   
    -- Applying a CASE statement to categorize products by ProductLine
    CASE ProductLine  
        -- If ProductLine is 'R', label the product as 'Road'
        WHEN 'R' THEN 'Road'  
        -- If ProductLine is 'M', label the product as 'Mountain'
        WHEN 'M' THEN 'Mountain'  
        -- If ProductLine is 'T', label the product as 'Touring'
        WHEN 'T' THEN 'Touring'  
        -- If ProductLine is 'S', label the product as 'Other sale items'
        WHEN 'S' THEN 'Other sale items'  
        -- Default condition: If ProductLine is none of the above, label the product as 'Not for sale'
        ELSE 'Not for sale'  
    END "Category",  
    -- Selecting the Name column
    Name  
-- Selecting data from the Product table
FROM Production.Product  
-- Ordering the result set by ProductNumber
ORDER BY ProductNumber;

Explanation:

• This SQL code selects specific columns from the Product table and categorizes products by their ProductLine.
• The CASE statement categorizes products into different categories based on their ProductLine values.
• If ProductLine is 'R', the product is labeled as 'Road'.
• If ProductLine is 'M', the product is labeled as 'Mountain'.
• If ProductLine is 'T', the product is labeled as 'Touring'.
• If ProductLine is 'S', the product is labeled as 'Other sale items'.
• If ProductLine is none of the specified values, the product is labeled as 'Not for sale'.
• The result set includes columns for ProductNumber, the categorized Category, and Name of the products.
• The result set is ordered by ProductNumber.

Sample Output:

productnumber|Category        |name                            |
-------------+----------------+--------------------------------+
AR-5381      |Not for sale    |Adjustable Race                 |
BA-8327      |Not for sale    |Bearing Ball                    |
BB-7421      |Not for sale    |LL Bottom Bracket               |
BB-8107      |Not for sale    |ML Bottom Bracket               |
BB-9108      |Not for sale    |HL Bottom Bracket               |
BC-M005      |Mountain        |Mountain Bottle Cage            |
BC-R205      |Road            |Road Bottle Cage                |
BE-2349      |Not for sale    |BB Ball Bearing                 |
BE-2908      |Not for sale    |Headset Ball Bearings           |
BK-M18B-40   |Mountain        |Mountain-500 Black, 40          |
BK-M18B-42   |Mountain        |Mountain-500 Black, 42          |
BK-M18B-44   |Mountain        |Mountain-500 Black, 44          |

...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Display the list price as a comment based on the price range.
Next: Evaluate whether the values in two columns are the same.