How to add 2 columns/multiple elements FOR XML PATH

Question

CREATE TABLE Products
(
    Customerkey int NOT NULL PRIMARY KEY,
    processdate date NULL,
    CCcount int NULL,
    CHKcount int NULL,
    SACount int NULL,
    VEHCount int NULL, 
    LoanCount int NULL,  
    MortCount int NULL
);

INSERT INTO products (Customerkey, processdate, CCcount, CHKCount, SACount, VEHCount, LoanCount,  MortCount) 
VALUES (101, '20210501', 12, 3, 5, 1, 3, 1),
       (102, '20210203', 1, 3, 1, 0, 0, 0),
       (103, '20190412', 4, 0, 2, 0, 3, 1)

SELECT 
    Customerkey, processdate,
    REPLACE(x.query('data(/root/*)').value('text()[1]', 'VARCHAR(100)'), SPACE(1), ',') AS Product_Mix,
    REPLACE(x.query('for $x in /root/*[./text()!=""]
    return local-name($x)').value('text()[1]', 'VARCHAR(100)'), SPACE(1), '+') AS Product_Mix_Expanded
FROM 
    @tbl
CROSS APPLY 
    (SELECT CCcount, CHKCount, SACount
     FOR XML PATH(''), TYPE, ROOT('root')) AS t(x);

I want LoanProduct_Mix for these VEHCount, LoanCount, MortCount. This is from this link SQL Server 2016: how to get a single row view

Expected output:

CustomerKey	processdate	Product_Mix	Product_Mix_Expanded	LoanProduct_Mix
101	2021-05-01	12,3,5	CCcount+CHKCount+SACount	VEHCount+LoanCount+MortCount
102	2021-02-03	1,3,1	CCcount+CHKCount+SACount
103	2019-04-12	4,0,2	CCount + SACount	LoanCount + MortCount

Yitzhak Khabinsky · Accepted Answer · 2021-06-23 17:13:40Z

Please try the following solution for SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE
(
    Customerkey int NOT NULL PRIMARY KEY,
    processdate date NULL,
    CCcount int NULL,
    CHKcount int NULL,
    SACount int NULL,
    VEHCount int NULL, 
    LoanCount int NULL,  
    MortCount int NULL
);
INSERT INTO @tbl (Customerkey, processdate, CCcount, CHKCount, SACount, VEHCount, LoanCount,  MortCount) 
VALUES (101, '20210501', 12, 3, 5, 1, 3, 1),
       (102, '20210203', 1, 3, 1, 0, 0, 0),
       (103, '20190412', 4, 0, 2, 0, 3, 1)
-- DDL and sample data population, end

SELECT Customerkey, processdate
    , REPLACE(x.query('data(/root/*)').value('text()[1]', 'VARCHAR(100)'), SPACE(1), ',') AS Product_Mix
    , REPLACE(x.query('for $x in /root/*[./text()!="0"]
        return local-name($x)').value('text()[1]', 'VARCHAR(100)'), SPACE(1), '+') AS Product_Mix_Expanded
    , REPLACE(y.query('for $x in /root/*[./text()!="0"]
        return local-name($x)').value('text()[1]', 'VARCHAR(100)'), SPACE(1), '+') AS LoanProduct_Mix
FROM @tbl
CROSS APPLY 
    (SELECT CCcount, CHKCount, SACount
     FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
CROSS APPLY 
    (SELECT VEHCount, LoanCount, MortCount
     FOR XML PATH(''), TYPE, ROOT('root')) AS t2(y);

Output

+-------------+-------------+-------------+--------------------------+------------------------------+
| Customerkey | processdate | Product_Mix |   Product_Mix_Expanded   |       LoanProduct_Mix        |
+-------------+-------------+-------------+--------------------------+------------------------------+
|         101 | 2021-05-01  |      12,3,5 | CCcount+CHKCount+SACount | VEHCount+LoanCount+MortCount |
|         102 | 2021-02-03  |       1,3,1 | CCcount+CHKCount+SACount | NULL                         |
|         103 | 2019-04-12  |       4,0,2 | CCcount+SACount          | LoanCount+MortCount          |
+-------------+-------------+-------------+--------------------------+------------------------------+

Thom A · Accepted Answer · 2021-06-23 16:03:05Z

0

I don't see any reason for XML here. CONCAT_WS with some CASE expressions seems to do the job nicely:

SELECT Customerkey,
       processdate,
       CONCAT_WS(',',CCcount,CHKcount,SACount) AS ProductMix,
       CONCAT_WS(' + ',CASE WHEN CCcount> 0 THEN 'CCcount' END,CASE WHEN CHKcount> 0  THEN 'CHKcount' END,CASE WHEN SACount> 0  THEN 'SACount' END) AS ProductMixExpanded,
       CONCAT_WS(' + ',CASE WHEN VEHCount> 0 THEN 'VehCount' END,CASE WHEN LoanCount> 0  THEN 'LoanCount' END,CASE WHEN MortCount> 0  THEN 'MortCount' END) AS LoanProductMix
FROM dbo.Products;

answered Jun 23, 2021 at 16:03

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

2 Comments

Yitzhak Khabinsky Over a year ago

The OP has SQL Server 2016. The CONCAT_WS() function is not available.

Thom A Over a year ago

No I, it was added in 2017, but the OP hasn't noted their version above @YitzhakKhabinsky, so I assume they are using the latest tools.

Collectives™ on Stack Overflow

How to add 2 columns/multiple elements FOR XML PATH

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related