CODEWITHSUNDEEP
sql-servert-sql
SidCharming
SidCharming

Reputation: 25

T-SQL concatenate multiple records into one record

I want to consolidate the records from the following table into a single record per PolNo, Year, Platform, Number. 

| PolNo | YEAR | Platform | Number | Record | memo                |
|-------|------|----------|--------|--------|---------------------|
| 123   | 2010 | pc       | 1      | 1      | The user had issues |
| 123   | 2010 | pc       | 1      | 2      | with the os.        |
| 123   | 2009 | pc       | 2      | 1      | Replaced RAM        |
| 123   | 2010 | mac      | 1      | 1      | Ordered new CDs     |
| 564   | 2009 | mac      | 1      | 1      | Broken CD TRAY      |
| 564   | 2010 | mac      | 1      | 1      | USB port dead       |
| 564   | 2010 | pc       | 1      | 1      | Ordered replacement |
| 564   | 2010 | pc       | 1      | 2      | laptop              |

The records will be consolidated into a single record (Record Column will not carry forward). Also at the same time the PolNo, Year, Platform and Number are concatenated into an ID rather than individual columns.

| ID  | YEAR | Platform | memo                             |
|-----|------|----------|----------------------------------|
| 123 | 2010 | pc-1     | The user had issues with the os. |
| 123 | 2009 | pc-2     | Replaced RAM                     |
| 123 | 2010 | mac-1    | Ordered new CDs                  |
| 564 | 2009 | mac-1    | Broken CD TRAY                   |
| 564 | 2010 | mac-1    | USB port dead                    |
| 564 | 2010 | pc-1     | Ordered replacement laptop       |

As you can see I joined the records in line 1 and line 6 above into one continuous memo field. I however have some memo fields that have 21 or 22 records to combine/join.

Not sure how I am going to make this happen.

Thinking cursor, but I don't have much experience and hear it's not effective. The table has around 64k rows to manipulate (which 22k of those have more than one record)

Upvotes: 1

Views: 380

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10610

You can use the old 'FOR XML' trick to aggregate the record text:

CREATE TABLE #Records
(
    PolNo SMALLINT
    , Year SMALLINT
    , Platform NVARCHAR(16)
    , Number TINYINT
    , Record TINYINT
    , Memo NVARCHAR(256)
);

INSERT INTO #Records(PolNo, Year, Platform, Number, Record, Memo)
VALUES
    (123, 2010, 'pc', 1, 1, 'The user had issues')
    ,(123, 2010, 'pc', 1, 2, 'with the os.')
    ,(123, 2009, 'pc', 2, 1, 'Replaced RAM')
    ,(123, 2010, 'mac', 1, 1, 'Ordered new CDs')
    ,(564, 2009, 'mac', 1, 1, 'Broken CD TRAY')
    ,(564, 2010, 'mac', 1, 1, 'USB port dead')
    ,(564, 2010, 'pc', 1, 1, 'Ordered replacement')
    ,(564, 2010, 'pc', 1, 2, 'laptop')

SELECT *
FROM #Records;

WITH RecordIdentifier AS
(
    SELECT PolNo, Year, Platform, Number
    FROM #Records
    GROUP BY PolNo, Year, Platform, Number
)

SELECT CONCAT(PolNo, '-', Year, '-', Platform, '-', Number) AS Id
    , (SELECT ' ' + Memo
            FROM #Records forAggregation
            WHERE forAggregation.PolNo = record.PolNo
                AND forAggregation.Year = record.Year
                AND forAggregation.Platform = record.Platform
                AND forAggregation.Number = record.Number
            ORDER BY forAggregation.Record
            FOR XML PATH ('')
            ) AS Memo
FROM RecordIdentifier record
ORDER BY Id

DROP TABLE #Records

Upvotes: 2

Related Questions