SQL - Combine Multiple Columns with Multiple Rows into one row

Question

What I'm trying to do: I have records in a SQL table where there are 5 columns and thousands of rows. The rows share duplicate data (i.e. account number) but what makes each unique is that data in one of the columns is different.

As an example:

col1|col2|col3|col4|col5
------------------------
123|abc|456|def|789
123|abc|456|def|date

But the columns can have different values, not necessarily always in column 5.

Here's what I started with:

SELECT TOP (15) stuff((
            SELECT ', ' + te.[accountid]
                ,te.[char1]
                ,te.[date]
                ,te.[date2]
                ,te.[char2]
            FROM D AS te
            INNER JOIN D AS tue ON tue.[accountid] = te.[accountid]
            WHERE tue.[accountid] = ue.[accountid]
            FOR XML path('')
                ,type
            ).value('.', 'varchar(max)'), 1, 2, '') AS ifile
FROM D AS ue
GROUP BY ue.[accountid]

But I get a monster long string that includes the duplicate rows in one column. I'm not sure what else to try so any insight would be appreciated.

What is your desired output?

Tab Alleman
– Tab Alleman

2015-04-07 13:49:44 +00:00
Commented Apr 7, 2015 at 13:49 — Tab Alleman
– Tab Alleman, Commented Apr 7, 2015 at 13:49
The desired output is: 123|abc|456|def|789|date

jmc
– jmc

2015-04-07 14:12:52 +00:00
Commented Apr 7, 2015 at 14:12 — jmc
– jmc, Commented Apr 7, 2015 at 14:12

Gordon Linoff · Accepted Answer · 2015-04-07 13:33:44Z

0

If I had to guess, you have an unnecessary self join in the subquery:

SELECT TOP (15) stuff((
            SELECT ', ' + te.[accountid], te.[char1], te.[date], te.[date2], te.[char2]
            FROM D te
            WHERE te.[accountid] = ue.[accountid]
            FOR XML path(''), type
           ).value('.', 'varchar(max)'), 1, 2, '') AS ifile
FROM D ue
GROUP BY ue.[accountid];

You might also want SELECT DISTINCT in the subquery.

answered Apr 7, 2015 at 13:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

jmc Over a year ago

Thanks for the response. I went ahead and modified my query based on your suggestion and it works but it duplicates the data between the commas based on how many row entries there are per account id. Per Tab Alleman's question, my desired out is: 123|abc|456|def|789|date

Tab Alleman · Accepted Answer · 2015-04-07 14:33:06Z

Use UNION to get rid of all the duplicate values and use your FOR XML PATH on the output to append it to a single string:

SELECT TOP (15) stuff((
            SELECT ', ' + CAST(te.[accountid] AS varchar(255)) FROM D
            UNION 
            SELECT ', ' + CAST(te.[char1] AS varchar(255)) FROM D
            UNION
            SELECT ', ' + CAST(te.[date] AS varchar(255)) FROM D
            UNION 
            SELECT ', ' + CAST(te.[date2] AS varchar(255)) FROM D
            UNION
            SELECT ', ' + CAST(te.[char2] AS varchar(255)) FROM D
            FOR XML path('')
                ,type
            ).value('.', 'varchar(max)'), 1, 2, '') AS ifile

Untested, treat as pseudo-code to give the general idea.

Collectives™ on Stack Overflow

SQL - Combine Multiple Columns with Multiple Rows into one row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related