[SQL2017] CONCAT and CONCAT_WS w/ STRING_AGG

/*******************************************************************************************************************
-- Title : [SQL2017] CONCAT and CONCAT_WS w/ STRING_AGG
-- Reference : docs.microsoft.com
-- Key word : concat concat() concat_ws concat_ws() string_agg string_agg() 멀티값 싱글값 멀티 로우 싱글 로우

                  단일값 string aggregation
*******************************************************************************************************************/

use tempdb;
go
 
 
------------------------------
-- CONCAT
------------------------------
select concat('aaa', 'bbb', null, 'ccc') 
    /* aaabbbccc */
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;  
    /* Happy Birthday 11/25 */
 
 
------------------------------
-- CONCAT_WS (separator, argument1, argument1 [, argumentN]… ) 
------------------------------
SELECT CONCAT_WS( ' - ', 'AAA', 'BBB', NULL, 'DDD') AS DatabaseInfo
    /* AAA - BBB - DDD */
SELECT CONCAT_WS( ' - ', 1, 2, 3, 'DDD') AS DatabaseInfo
    /* 1 - 2 - 3 - DDD */
 
 
------------------------------
-- CONCAT_WS w/ STRING_AGG
------------------------------
SELECT CONCAT_WS( '~', database_id, ISNULL(recovery_model_desc,''), ISNULL(containment_desc,'N/A'), ';') AS DatabaseInfo
FROM sys.databases;
    /*
    1~SIMPLE~NONE~;
    2~SIMPLE~NONE~;
    3~FULL~NONE~;
    4~SIMPLE~NONE~;
    5~FULL~NONE~;
    */
SELECT STRING_AGG( CONCAT_WS( '~', database_id, ISNULL(recovery_model_desc,''), ISNULL(containment_desc,'N/A'), ';')
                 , char(13)
                 ) AS DatabaseInfo
FROM sys.databases;
    /*
    1~SIMPLE~NONE~; 2~SIMPLE~NONE~; 3~FULL~NONE~; 4~SIMPLE~NONE~; 5~FULL~NONE~;
    */