Group cols in row in SQL PostgreSQL

Question

Need group 3 col in 1 row

I have this table:

This query:

select cidade, grupo from dados;

|----------|--------|  
|Cidade    |Grupo   |  
|----------|--------|  
|varginha  |grande  |  
|----------|--------|  
|mimoso    |pequena |  
|----------|--------|  
|Sao Tumas |media   |  
|----------|--------|

But, I need convert, or grouping cols in row in SQL, like this:

|---------|  
|Grouping |    
|---------|  
|varginha |  
|grande   |  
|---------|  
|mimoso   |     
|pequena  |  
|---------|  
|Sao Tumas|  
|media    |  
|---------|

In your resultset, does each block (like varginha grande) represents a single record, or two different records? — GMB
– GMB, Commented Nov 9, 2019 at 16:27
each line is single record. like: line 1 - varginha, grande / line 2 - mimoso pequena / line 3 - sao tumas, media — Alison Ribeiro
– Alison Ribeiro, Commented Nov 9, 2019 at 17:17
OK. So what is wrong with the answer by Gordon Linoff? It seems to return the proper results for your sample data. — GMB
– GMB, Commented Nov 9, 2019 at 17:21

hkoosha · Accepted Answer · 2019-11-09 17:37:42Z

1

What you are trying to do is called concatenation in SQL realm. GROUP BY is completely a different beast (more info).

In your case, you want to put a new line in between: first column + new line + second column. In Postgres, using chr(10) you'll get the new line character, so:

postgres=# SELECT CONCAT("cidade", CHR(10), "grupo") AS "Grouping" FROM "dados";

 Grouping
----------
varginha
grande

mimoso
pequena

Sao Tumas
media
(3 rows)

answered Nov 9, 2019 at 17:37

hkoosha

1,1453 gold badges15 silver badges33 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-11-09 15:34:02Z

0

Are you just looking for concat():

select (cidade || ' ' || grupo) as grouping
from t;

You can put a new line as the separator. Usually, I don't recommend this unless you have a good reason.

answered Nov 9, 2019 at 15:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Alison Ribeiro Over a year ago

I need the convert results of 2 cols in 2 rows. concat not work for me.

Gordon Linoff Over a year ago

@AlisonRibeiro . . . Your data has 2 columns and 3 rows. The result set looks like it has 3 rows, so your comment does not make sense.

Alison Ribeiro · Accepted Answer · 2019-11-19 02:52:36Z

0

Well... I Resolve this question using UNION ALL

Like this:

select cidade as col_data from dados union all select grupo as col_data from dados;

answered Nov 19, 2019 at 2:52

Alison Ribeiro

691 silver badge5 bronze badges

Collectives™ on Stack Overflow

Group cols in row in SQL PostgreSQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related