CODEWITHSUNDEEP
sqldatabaseoracle-database
Nina PRO
Nina PRO

Reputation: 91

How write sql statement to have in one line the same ID

I have a problem how can we write sql statement in this:

I have this: 

id        name       color
 1         A          blue
 3         D          pink
 1         C          grey
 3         F          blue
 4         E          red

and I want my result to be like this:

id        name    name    color     color
 1        A        C      blue       grey
 3        D        F      pink       blue
 4        E               red

How can I do that in SQL? your help is very appreciated

Thank you

Upvotes: 0

Views: 826

Answers (3)

Nina PRO
Nina PRO

Reputation: 91

The answer of @MT0 worked perfectly, there is another alternative is using pivot, there is the SQL statement:

SELECT * 
FROM
(
SELECT id, name, color
FROM table 

WHERE  -clause where-
 )
PIVOT 
(
max(color)
FOR name in ('A','D','E') 
 ) ;

Thank you

Upvotes: 0

MT0
MT0

Reputation: 167972

Query - Concatenate the values into a single column:

SELECT ID,
       LISTAGG( Name,  ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS Names,
       LISTAGG( Color, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS Colors
FROM   table_name
GROUP BY ID;

Output:

ID Names Colors
-- ----- ---------
 1 A,C   blue,grey
 3 D,F   pink,blue
 4 E     red

Query - If you have a fixed maximum number of values:

SELECT ID,
       MAX( CASE rn WHEN 1 THEN name  END ) AS name1,
       MAX( CASE rn WHEN 1 THEN color END ) AS color1,
       MAX( CASE rn WHEN 2 THEN name  END ) AS name2,
       MAX( CASE rn WHEN 2 THEN color END ) AS color2
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS rn
  FROM   table_name t
)
GROUP BY id;

Output:

ID Name1 Color1 Name2 Color2
-- ----- ------ ----- ------
 1 A     blue   C     grey
 3 D     pink   F     blue
 4 E     red

Upvotes: 2

Zahiro Mor
Zahiro Mor

Reputation: 1718

there is a fundamental problem with what you are trying to achieve - you do not know how many values of names (/ color) per id to expect - so you do not know how many columns the output should be..... a workaround would be to keep all the names (and colors) per id in one column :

select id,group_concat(name),group_concat(color) from tblName group by id

Upvotes: 1

Related Questions