How to select data from a column as columns in SQL?

Asked

Viewed 1,533 times

1

It is possible to make a query (SELECT) of data returned from a column such as columns?

select questores from table;

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

Ex: This row below would be data returned from a column query:

How could I ever return dice as columns in this way

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

The idea is to return the data of this spine as columns and then make a assign data to those columns that were generated by the query represented as data. At the end would be 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

  • You want to select the table structure?

  • Possible duplicate of How to get column names from a table in SQL Server?

  • Good evening Brendow Adriel! There is no way to do this directly with the SQL query. To put the query data as columns, you need to make the normal query and treat it a font-end

  • Brendow, you need to use a programming language to mount an array with the values of this query and print these values in a front-end table.

  • 2

    research on pivot and that depends a lot on which database you’re using. Perhaps an easier way would be to mount the query with multiple sub-selects at runtime. Anyway, the question does not bring enough details to elaborate an answer

  • I want to get the values of a column selected as columns.

  • @Brendowadriel Avoid so expressively changing the content of your question, it causes a lot of confusion and makes it difficult to get a more precise answer.

  • 1

    @Brunomaciel, it is possible yes, but it depends.

  • Which database do you use (SGDB)? Are the data fixed or dynamic? Do you already know how to do the export to Excel part? How?

  • I am currently using Mysql. From this specific table are physical data, it is a table of questions. Exporting to xsl or csv is no problem, problem is selecting the data in this format in the database query.

Show 5 more comments

2 answers

1

Assuming your structure and your data are 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 the type array, look at you:

SELECT array_agg( nome ) FROM tb_foobar;

Exit:

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

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

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

Exit:

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

Sqlfiddle: http://sqlfiddle.com/#! 17/2aa66/7

  • 2 answers to the same question?

  • @Don'tPanic The question suffered "heavy" issues, I preferred not to change the answer and includes a new.

1

In Postgres this information may be obtained from information_schema, of a VIEW calling for columns.

For example, consider the table tb_foobar:

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

Searching all table columns:

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

Exit:

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

Sqlfiddle: http://sqlfiddle.com/#! 17/686b1/1

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.