SQL transform distinct rows into one row and multiple columns

Question

I have a table and rows with the same Name can occur like up to 5 times. Example:

| Name   | Value   |
|--------|---------|
| Test   | Value1  |
| Test   | Value2  |
| Test   | Value3  |
| FooBar | Value11 |
| FooBar | Value12 |

I am trying to create a query to compress the rows to have a unique Name and transfer the values to columns. If there are less than 5 values per name the remaining columns should have NULL.

| Name   | Col1    | Col2    | Col3   | Col4 | Col5 |
|--------|---------|---------|--------|------|------|
| Test   | Value1  | Value2  | Value3 | NULL | NULL |
| FooBar | Value11 | Value12 | NULL   | NULL | NULL |

I looked at Pivot but I don't have a column to aggregate.
I need this format for csv file.
Using SQL Server 2016.

Gordon Linoff · Accepted Answer · 2021-04-10 15:03:51Z

2

You can construct a column using row_number():

select name,
       max(case when seqnum = 1 then value end) as value_1,
       max(case when seqnum = 2 then value end) as value_2,
       max(case when seqnum = 3 then value end) as value_3,
       max(case when seqnum = 4 then value end) as value_4,
       max(case when seqnum = 5 then value end) as value_5
from (select t.*,
             row_number() over (partition by name order by value) as seqnum
      from t
     ) t
group by name;

answered Apr 10, 2021 at 15:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL transform distinct rows into one row and multiple columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related