1

So, I have two tables: dataTable and dataTableColNames.

dataTable goes like this: 

col1 | col2 | col3 | ... | col9
-----|------|------|-----|-----------
123  | abcd | text | ... | 02/02/2018
 ... | ...  | ...  | ... | ...
999  | xyz  | text | ... | 31/12/2018

and has about 30 rows.

dataTableColNames is something like this: 

colID | colNameEn       | colNameRu
------|-----------------|--------------
col1  | "Column 1 Name" | "Колонка 1"
...   | ...             | ...
col9  | "Column 9 Name" | "Колонка 9"

and could contain column names even 5 or more words long. Also, there could be different translations for column titles for the same data.

How to make a SELECT query to get a table like this? 

Column 1 name | ... | Column 9 Name
--------------|-----|--------------
123           | ... | 02/02/2018

PL/SQL Developer v10

This question is not quite duplicate because my tables do not have anything like entity_id nor any foreign keys. Also, it is about column aliases, not querying different values from many different tables. Or is it? Anyway, I don't see a solution for my problem in answers for this question. Or maybe don't understand. How to implement it on my problem?

VELFR
  • 407
  • 5
  • 21
  • 2
    It is pointless to have a table dataTableColNames just to store column aliases. Basically, you can select them simply as `select col1 as "Column 1 Name", col2 as "Column 2 Name" .. ` – Kaushik Nayak Jun 14 '18 at 14:22
  • What if i have much more columns? Or names are very long - like, about a sentence. – VELFR Jun 14 '18 at 14:27
  • Even then it would be a very bad design. You may eventually get a solution here from someone, but it would be pointless to perform all that operation ( probably with Unpivot and Pivot) just to show aliases. Personally, I'm reluctant to spend my time just to give someone such a solution. anyways, best of luck! – Kaushik Nayak Jun 14 '18 at 14:34
  • Thanks anyway. GLHF) Would like to know which DB design with long column names and translations could be better. – VELFR Jun 14 '18 at 14:38
  • 1
    I can see the advantages of this design–it's flexible and dynamic and enables arbitrary column name values to be stored as data rather than being hardcoded into sql – varontron Jun 14 '18 at 15:33

1 Answers1

1

A solution you might try is to UNION two queries together, the first being from dataTableColNames and the second from dataTable. The first query uses a subquery which joins dataTableColNames to itself for each column, and then the outer query serves to pivot the column name rows into columns. The second query returns the data. The query below is untested. You will also need to use either your sql tool to suppress the column name headers (col1, col2, col3) returned to the console or gui, or post-process the results to do so in your client or middleware.

SELECT col1, col2, col3
FROM (
 SELECT c1.col1Name as col1, c2.col2Name as col2, c3.col3Name as col3
   FROM dataTableColNames c1
   JOIN dataTableColNames c2 on c1.colId = c2.colId
   JOIN dataTableColNames c3 on c1.colId = c3.colId
  WHERE c1.colId = 'col1'
    AND c2.colId = 'col2'
    AND c3.colId = 'col3' 
) t1
UNION
SELECT col1, col2, col3 from dataTable
varontron
  • 1,120
  • 7
  • 21