T-SQL Append String Field to Column Where Exists in Another Column

Question

I want to append AttorneyList into the second result set for AttorneyList with the same corresponding DateFDdue. How do I do that using a T-SQL query? Also, is it possible to sort the new appended AttorneyList?

Result Set 1

+-------------------+-----------------------------------+
|     DateFDdue     |           AttorneyList            |
+-------------------+-----------------------------------+
| September 8, 2015 |  Vitali (Purple) (Orange) (Lorah) |
+-------------------+-----------------------------------+
| September 9, 2015 |  Ricords (Purple) (Orange) (OOR)  |
+-------------------+-----------------------------------+

Result Set 2

+--------------------+-------------------------------------+
|     DateFDdue      |            AttorneyList             |
+--------------------+-------------------------------------+
| September 3, 2015  |  Mlinarich (Higgins); Riches (OOR)  |
+--------------------+-------------------------------------+
| September 4, 2015  |  APSCUF (Higgins); APSCUF (Higgins) |
+--------------------+-------------------------------------+
| September 8, 2015  |  Brown (Singh); Burda (Zeppos)      |
+--------------------+-------------------------------------+
| September 9, 2015  |  Gay (OOR); Graves (OOR)            |
+--------------------+-------------------------------------+
| September 10, 2015 |  Burda (Higgins); WFMZ-TV (Young)   |
+--------------------+-------------------------------------+

In other words, how do I combine both result sets to get:

Final Result

+--------------------+-------------------------------------+
|     DateFDdue      |            AttorneyList             |
+--------------------+-------------------------------------+
| September 3, 2015  |  Mlinarich (Higgins); Riches (OOR)  |
+--------------------+-------------------------------------+
| September 4, 2015  |  APSCUF (Higgins); APSCUF (Higgins) |
+--------------------+-------------------------------------+
| September 8, 2015  |  Brown (Singh); Burda (Zeppos); Vitali (Purple (Orange) (Lorah)         |                                     |
+--------------------+-------------------------------------+
| September 9, 2015  |  Gay (OOR); Graves (OOR); Ricords (Purple (Orange) (OOR)                    |                                     |
+--------------------+-------------------------------------+
| September 10, 2015 |  Burda (Higgins); WFMZ-TV (Young)   |
+--------------------+-------------------------------------+

Just keep the data as separate rows and do the xml path to get them into same list later? Or what is the actual question here... — James Z
– James Z, Commented Sep 3, 2015 at 15:10
Don't keep multiple values in a single field. That's a fundamental SQL Anti-Pattern. Each value should have its own field or its own row. — MatBailie
– MatBailie, Commented Sep 3, 2015 at 15:12
Agreed with @MatBailie. You're going to have lots of headaches in the future if you don't keep your data normalised. — John Bell
– John Bell, Commented Sep 3, 2015 at 16:02
I only want to combine the result sets using one T-SQL query as mentioned in the question above. Thanks. — David
– David, Commented Sep 3, 2015 at 16:29

Kevin Suchlicki · Accepted Answer · 2015-09-03 17:10:53Z

2

This will bring in dates / attorneys in either list, concatenating when the same date is in both lists:

SELECT  ISNULL(rs1.DateFDdue, rs2.DateFDdue) AS DateFDdue
        , ISNULL(rs1.AttorneyList, '') + ' ' + ISNULL(rs2.AttorneyList, '') AS AttorneyList
FROM    ResultSet1 rs1
        FULL OUTER JOIN ResultSet2 rs2 ON rs1.DateFDdue = rs2.DateFDdue;

answered Sep 3, 2015 at 17:10

Kevin Suchlicki

3,1542 gold badges17 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Thank you for solving my original issuse!

Rubens Farias · Accepted Answer · 2015-09-03 17:26:24Z

To sort the rows, you'll need to convert the varchar value to datetime

;
WITH    ResultSet1  AS
(
        SELECT  CONVERT(NVARCHAR(20), DateFDdue) DateFDdue, AttorneyList
        FROM
        (       VALUES
                ('September 8, 2015', 'Vitali (Purple) (Orange) (Lorah)'),
                ('September 9, 2015', 'Ricords (Purple) (Orange) (OOR)')
        )       ResultSet(DateFDdue, AttorneyList)
),      ResultSet2  AS
(
        SELECT  CONVERT(NVARCHAR(20), DateFDdue) DateFDdue, AttorneyList
        FROM
        (       VALUES
                ('September 3, 2015', 'Mlinarich (Higgins); Riches (OOR)'),
                ('September 4, 2015', 'APSCUF (Higgins); APSCUF (Higgins'),
                ('September 8, 2015', 'Brown (Singh); Burda (Zeppos)'),
                ('September 9, 2015', 'Gay (OOR); Graves (OOR)'), 
                ('September 10, 2015', 'Burda (Higgins); WFMZ-TV (Young)')
        )       ResultSet(DateFDdue, AttorneyList)
),      FinalResult AS
(
        SELECT  ISNULL(ResultSet1.DateFDdue, ResultSet2.DateFDdue) AS DateFDdue,
                ISNULL(ResultSet1.AttorneyList, '') + ' ' + 
                ISNULL(ResultSet2.AttorneyList, '') AS AttorneyList
        FROM    ResultSet1
        FULL    OUTER JOIN ResultSet2 
            ON  ResultSet1.DateFDdue = ResultSet2.DateFDdue
)
SELECT      *
FROM        FinalResult
ORDER BY    CONVERT(datetime, DateFDdue)

Collectives™ on Stack Overflow

T-SQL Append String Field to Column Where Exists in Another Column

Result Set 1

Result Set 2

Final Result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Result Set 1

Result Set 2

Final Result

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related