AdventureWorks Database: Concatenate the columns separated by a unique character

SQL Query - AdventureWorks: Exercise-61 with Solution

61. Write a SQL query that concatenate the columns name, productnumber, colour, and a new line character from the following table, each separated by a specified character.

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 a concatenated string containing product details separated by commas, with a line break character at the end
SELECT CONCAT_WS( ',', name, productnumber, color,chr(10)) AS DatabaseInfo

-- From the Production schema's Product table
FROM production.product;

Explanation:

• The SQL query retrieves data from the Product table within the Production schema.
• It constructs a concatenated string using the CONCAT_WS() function to combine various elements such as name, product number, and color, separated by commas.
• The CONCAT_WS() function concatenates multiple strings together with a specified separator.
• In the concatenated string:
• name represents the product name.
• productnumber represents the product number.
• color represents the color of the product.
• chr(10) represents a line break character, ensuring that each concatenated string ends with a line break.
• The concatenated string is aliased as 'DatabaseInfo'.

Sample Output:

databaseinfo                             |
-----------------------------------------+
Adjustable Race,AR-5381,¶                |
Bearing Ball,BA-8327,¶                   |
BB Ball Bearing,BE-2349,¶                |
Headset Ball Bearings,BE-2908,¶          |
Blade,BL-2036,¶                          |
LL Crankarm,CA-5965,Black,¶              |
ML Crankarm,CA-6738,Black,¶              |
HL Crankarm,CA-7457,Black,¶              |
Chainring Bolts,CB-2903,Silver,¶         |
Chainring Nut,CN-6137,Silver,¶           |
Chainring,CR-7833,Black,¶                |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Join the name, color etc.
Next: Return the five leftmost characters of each product name.