AdventureWorks Database: Return names and modified date separated by commas

Last update on December 20 2024 11:57:57 (UTC/GMT +8 hours)

74. From the following table write a query in SQL to return the names and modified date separated by commas in a single row.

Sample table: Person.Person

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Concatenating the 'FirstName', 'LastName', and 'ModifiedDate' columns along with additional text,
-- separated by a comma and space, and aliasing the result as 'test'
SELECT STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), ', ') AS test 

-- From the Person schema's Person table
FROM Person.Person;

Explanation:

The SQL query retrieves data from the Person table within the Person schema.
The CONCAT() function concatenates the 'FirstName', 'LastName', and 'ModifiedDate' columns along with additional text.
Each concatenated value includes the first name, last name, modified date enclosed in parentheses, and space-separated.
The STRING_AGG() function concatenates these concatenated values into a single string, separated by a comma and space.
The result of the concatenation is aliased as 'test'.

Sample Output:

test                                                                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Ken Sánchez (2009-01-07 00:00:00), Terri Duffy (2008-01-24 00:00:00), Roberto Tamburello (2007-11-04 00:00:00), Rob Walters (2007-11-28 00:00:00), Gail Erickson (2007-12-30 00:00:00), Jossef Goldberg (2013-12-16 00:00:00), Dylan Miller (2009-02-01 00:00:0|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Show 'N/A' instead of null and names separated by commas.
Next: Find email addresses of employees and groups them by city.