How to select data from a column as a column in SQL? [closed]

Question

How to select data from a column as a column in SQL? [closed]

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

Is it possible to query ( SELECT ) data returned from a column such as columns ?

select questors from table;

|   coluna    | → coluna
|-------------|
|    dado1    | → dados retornados da consulta
|    dado2    |                                                     
|    dado3    |

Ex: This line below would be returned from a one-column query:

How can I return the data as columns in this way?

|dado1| dado2 | dado3|
----------------------

The idea is to return the data from this column as columns and then make a data assignment for those columns that were generated by the query represented as data. In the end it would look something like this and export them to a spreadsheet in this format:

 |dado1 | dado2  | dado3 | → colunas
 ------------------------
 |valor1| valor1 | valor1| → dados de alguma outra consulta 
 |valor2| valor2 | valor2|
 |valor3| valor3 | valor3|

sql

asked by anonymous 24.05.2018 / 00:14

2 answers

Problem in storing combobox label in HTML Change div when click / mouse over with jquery

score 1 · Answer 1

As% of% this information can be obtained from Postgres , of a% called% information_schema .

For example, consider the VIEW table:

CREATE TABLE public.tb_foobar
(
  id INTEGER,
  name TEXT,
  moment DATE,
  flag BOOLEAN
);

Querying all the columns of the table:

SELECT
  column_name,
  data_type
FROM
  information_schema.columns
WHERE
  table_schema = 'public' AND
  table_name = 'tb_foobar';

Output:

| column_name | data_type |
|-------------|-----------|
|          id |   integer |
|        name |      text |
|      moment |      date |
|        flag |   boolean |

SQLFiddle: link

score 1 · Answer 2

Assuming your structure and data is something like:

CREATE TABLE tb_foobar
(
  id INTEGER PRIMARY KEY,
  nome TEXT
);


INSERT INTO tb_foobar ( id, nome ) VALUES ( 1, 'JESUS');
INSERT INTO tb_foobar ( id, nome ) VALUES ( 2, 'MARIA');
INSERT INTO tb_foobar ( id, nome ) VALUES ( 3, 'JOSE');
INSERT INTO tb_foobar ( id, nome ) VALUES ( 4, 'MADALENA');
INSERT INTO tb_foobar ( id, nome ) VALUES ( 5, 'JOAO');

In Postgres , you can use the aggregation function array_agg() to solve your problem, instead of creating a column for each table record, you can create a column of type array , see:

SELECT array_agg( nome ) FROM tb_foobar;

Output:

|                      array_agg |
|--------------------------------|
| JESUS,MARIA,JOSE,MADALENA,JOAO |

You can also use the string_agg() function to generate a string with the separator of your choice, for example:

SELECT string_agg( nome, ';' ) FROM tb_foobar;

Output:

|                     string_agg |
|--------------------------------|
| JESUS;MARIA;JOSE;MADALENA;JOAO |

SQLFiddle: link