2

I have 2 tables tnHeaders and tnData

tnHeaders

fnIDX fnDESCRIPTION
1 h1
2 h2
3 h3

tnData

fnIDX fnHEADER_IDX fnDESCRIPTION
1 1 d1
2 1 d2
3 1 d3
4 2 d4
5 2 d5
6 2 d6
7 3 d7
8 3 d8
9 3 d9

and would like to produce this output

fnOUTPUT
h1
d1
d2
d3
h2
d4
d5
d6
h3
d7
d8
d9

I can do this in code no problem, but how can I do this in SQL? (Make the server work)

forpas
  • 160,666
  • 10
  • 38
  • 76
pcurtis
  • 81
  • 5
  • Welcome to Stack Overflow. Please take the [tour] to learn how Stack Overflow works and read [ask] on how to improve the quality of your question. Then see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. – Progman Dec 25 '21 at 16:21

1 Answers1

1

You need to use UNION ALL for the descriptions of both tables and sort the results in such a way that headers are on top of their data:

SELECT fnOUTPUT
FROM (
  SELECT fnDESCRIPTION fnOUTPUT, fnIDX header_index, 1 is_header FROM tnHeaders
  UNION ALL
  SELECT fnDESCRIPTION, fnHEADER_IDX, 0 FROM tnData
) t
ORDER BY header_index, is_header DESC, fnOUTPUT;

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76