Combining Records in T-SQL Using CTEs with STUFF Function

Combining Records in TSQL

In this article, we’ll explore a common problem when working with large datasets in SQL Server using T-SQL. The goal is to combine all records after the first full record displayed in a specific column.

Background

When working with data from multiple tables, it’s not uncommon to encounter duplicate or redundant information. In this case, we’re dealing with a dataset that includes multiple rows for each item, but only wants to display the combined value of certain columns.

Sample Data

To illustrate this problem, let’s consider an example using three tables: BList, Location, and Inventory.

number	line	partNum
87	1	55G5

ID	Route
1	A

prodID	Name
1	Item1

The query to retrieve this data might look like this:

;WITH TheDATA AS (
   SELECT 
      BL.number                                  AS 'number', 
      BL.line                                    AS 'line', 
      BL.partNumber                              AS 'partNum',
      BL.phoneNumber                             AS 'phoneNum',
      L.qtySN                                    AS 'qty_SN',
      I.qtyProperty                              AS 'qty_Property',
      I.qtyName                                  AS 'qty_Name',
      I.qtyTime                                  AS 'qty_Time',
      ROW_NUMBER() Over (
         PARTITION BY 
             BL.number 
         ORDER BY
             BL.number
      )                                          AS 'RowNo'
   FROM
      BList                                      AS BL
   INNER JOIN Location                           AS L
      ON BL.ID                                    = L.Route
   INNER JOIN Inventory                          AS I
      ON L.ID                                     = I.prodID
)
SELECT
   number                                        AS 'number',
   IIF(RowNo = 1, CONVERT(varchar, line), '')    AS 'line',
   IIF(RowNo = 1, partNumber, '')                AS 'partNum',
   IIF(RowNo = 1, phoneNumber, '')               AS 'phoneNum',
   qty_SN                                        AS 'qty_SN',
   qty_Property                                  AS 'qty_Property',
   qty_Name                                      AS 'qty_Name',
   qty_Time                                      AS 'qty_Time'
FROM
   TheData

This query will return the first row of data for each item, which might not be what we want.

Solution

To solve this problem, we can use a Common Table Expression (CTE) to create a temporary result set that combines all records after the first full record displayed. We’ll use the STUFF function to concatenate the values in the desired columns.

CTE Query

;WITH CTE(number ,line ,partNum ,phoneNum     ,qty_SN ,qty_Property ,qty_Name ,qty_Time)
AS
(
SELECT 87     ,1    ,  '55G5'    ,'555-789-7512'    ,00123  ,'Local'        ,'Owner'    ,'05:22' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,14988  ,'Local'        ,'Seller'   ,'10:44' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,521    ,'Remote'       ,'Owner'    ,'01:05' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,50697  ,'Local'        ,'Seller'   ,'11:41' UNION ALL
SELECT 87     ,NULL    ,NULL       ,NULL        ,2359   ,'Remote'       ,'Seller'   ,'04:45'
)
SELECT * FROM
(              
SELECT DISTINCT  number, line,partNum ,phoneNum,
        STUFF((SELECT ', '+CAST(qty_SN AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_SN,
        STUFF((SELECT ', '+CAST(qty_Property AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Property,
        STUFF((SELECT ', '+CAST(qty_Name AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Name,
        STUFF((SELECT ', '+CAST(qty_Time AS Varchar(10)) FROM CTE FOR XML PATH ('')),1,1,'') AS qty_Time
FROM CTE
)dt
WHERE line IS NOT NULL OR partNum IS  NOT NULL OR phoneNum IS NOT NULL

This query uses a CTE to create a temporary result set that combines all records after the first full record displayed. The STUFF function is used to concatenate the values in the desired columns.

Explanation

We start by creating a CTE that selects the desired data and adds a row number column using the ROW_NUMBER() function.
We then use this CTE in our main query to select the distinct values for each item, excluding any NULL values.
The STUFF function is used to concatenate the values in the desired columns. This ensures that only one value per item appears in these columns.

Example Use Cases

This solution can be applied to a variety of scenarios where data needs to be combined or summarized. Here are some examples:

Combining order quantities for different items on an order.
Calculating shipping costs for multiple packages.
Displaying inventory levels for each item across multiple warehouses.

Conclusion

In this article, we’ve explored how to combine records in TSQL using a Common Table Expression (CTE) and the STUFF function. This solution can be applied to various scenarios where data needs to be combined or summarized, making it a powerful tool in any SQL Server developer’s toolkit.

Last modified on 2023-06-02