SQL - Converting multiple rows into multiple columns

Question

Need a simple help here. Have went though various solutions such as using case and row number, pivot but unable to find a suitable solution.

Basically I want to use the group the Name values and find all the non null and distinct value and display the results in a single row.

Input

Name | Color
John | Blue
John | Green
Mary | NULL
Mary | Yellow
Mary | Pink
Mary | Pink

Expected Output

Name | Color1 | Color2 | Color3
John | Blue   | Green  |  NULL
Mary | Yellow | Pink   |  NULL

Currently I've came up to the query below but still looks far from my desired outcome. Appreciate your assistance , thanks!

select  Name,
    max(case when seqnum = 1 then Color end) as Color1,
    max(case when seqnum = 2 then Color end) as Color2,
    max(case when seqnum = 3 then Color end) as Color3
from (select table.*, row_number() over (partition by Name order by Name) as seqnum
    from table
    ) table
group by Name

Which dbms are you using?

jarlh
– jarlh

2018-10-30 06:51:56 +00:00
Commented Oct 30, 2018 at 6:51 — jarlh
– jarlh, Commented Oct 30, 2018 at 6:51
Using SQL server

Kenny Tung
– Kenny Tung

2018-10-30 07:06:17 +00:00
Commented Oct 30, 2018 at 7:06 — Kenny Tung
– Kenny Tung, Commented Oct 30, 2018 at 7:06

Tim Biegeleisen · Accepted Answer · 2018-10-30 03:11:20Z

2

I think you should be using DENSE_RANK here, rather than ROW_NUMBER:

SELECT
    Name,
    MAX(CASE WHEN rnk = 1 THEN Color END) AS Color1,
    MAX(CASE WHEN rnk = 2 THEN Color END) AS Color2,
    MAX(CASE WHEN rnk = 3 THEN Color END) AS Color3
FROM
(
    SELECT *, DENSE_RANK() OVER (PARTITION BY Name
        ORDER BY CASE WHEN Color IS NOT NULL THEN 0 ELSE 1 END, Color) rnk
    FROM yourTable
) t
GROUP BY
    Name;

Demo

It isn't entirely clear what ordering you actually want to use for the colors, since in your expected output John's color are ascending while Mary's are descending. I chose to order ascending, with NULLs last.

edited Oct 30, 2018 at 3:11

answered Oct 30, 2018 at 3:07

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

Kenny Tung Over a year ago

Thanks. I kind of understand the concept now. Issue is because the table is joined with multiple tables, currently figuring out how to include the join statements

ferhado · Accepted Answer · 2018-10-30 03:38:14Z

0

You could get the result as json like in the example:

SELECT concat(
    '{',
    name,
    ':[',
    GROUP_CONCAT(color),
    ']}'
) FROM `test` GROUP BY name

Result:

{
  John: [Blue, Green]
}
{
  Mary: [NULL, Yellow, Pink, Pink]
}

Demo

edited Oct 30, 2018 at 3:38

answered Oct 30, 2018 at 3:25

ferhado

2,6042 gold badges17 silver badges38 bronze badges

1 Comment

jarlh Over a year ago

Apparently OP is using SQL Server. Will this still work?

Collectives™ on Stack Overflow

SQL - Converting multiple rows into multiple columns

2 Answers 2

Demo

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related