CODEWITHSUNDEEP
sql-serverpivot
JJ.
JJ.

Reputation: 9950

How to merge multiple values from multiple columns into one row?

I have this table:

I need to convert it to (with parenthesis as well):

row_nbr - row_label                     - default_order
10      - TOTAL ACCOUNTABLE GROSS       - (1, 3)
12      - DEDUCTIBLE TERMS              - (3)
20      - TOTAL DEDUCTIBLE TERMS        - (3)
34      - AMOUNT DUE (UNRECOUPED)       - (4)
36      - ACCOUNTABLE GROSS             - (2)
41      - TOTAL CONTINGENT COMPENSATION - (3)

I could have more than twice of the same row_nbr.

In this case the 10 is there twice, but I could have 3 10's, 4 12's, etc.

I kind of started the pivot table but honestly, even by looking at the Microsoft site, I cannot for the life of me figure this out.

select row_nbr, row_label, default_order
from #temp
pivot
(
  max(row_nbr)
  for default_order in (default_order)
) piv;

Anyone care to help?

Thanks.

Upvotes: 0

Views: 106

Answers (2)

Russell Fox
Russell Fox

Reputation: 5425

As @Vinit says, you can use the string_agg function in 2017, but if you're at least on 2005 you can use a horrible, torturous XML generator:

SELECT  row_nbr
       ,row_label
       ,default_order = '(' +
            STUFF( 
            (SELECT ', ' + CAST(default_order AS VARCHAR(10))
            FROM        #temp
            WHERE       row_nbr = t.row_nbr
            ORDER BY    default_order
            FOR XML PATH('') ,
                ROOT('MyString'),
                TYPE ).value('/MyString[1]', 'varchar(max)'), 1, 2, '')
            + ')'
FROM    #temp t;

You can read more about it in this blog post

Upvotes: 3

Xedni
Xedni

Reputation: 4695

PIVOTS are definitely wonky to get a grip on. Fortunately, in this case, while you could use one as an intermediate step, it's not necessary. PIVOT will take each value and put it into a corresponding distinct column, and what you're wanting is a single column, with them all concatenated together. Like I said, you could do the pivot, then just concatenate all the generated columns together, but that's way more work than is needed.

On 2014, the easiest way to do this is using FOR XML. Russell Fox's answer pretty much covers how that technique works (although there are a few variants on how you can do that should you so choose).

If you know definitively the values are all integers, you can save a bit of typing and omit the type and value operators, as those are only necessary when you have to escape certain XML characters in string fields

select
    row_nbr,
    row_label,
    default_order,
    stuff
    (
        (
            select concat(',', default_order)
            from #temp i
            where i.row_nbr = o.row_nbr
            for xml path('')
        ), 1, 1, '' 
    )

from #temp o

Upvotes: 0

Related Questions