Ggtcf

Was the picture area of a CRT a parallelogram (instead of a true rectangle)?

How to check participants in at events?

In Star Trek IV, why did the Bounty go back to a time when whales were already rare?

Are Warlocks Arcane or Divine?

Freedom of speech and where it applies

Teaching indefinite integrals that require special-casing

Reply ‘no position’ while the job posting is still there (‘HiWi’ position in Germany)

Giant Toughroad SLR 2 for 200 miles in two days, will it make it?

Have I saved too much for retirement so far?

Is there a good way to store credentials outside of a password manager?

Is it okay / does it make sense for another player to join a running game of Munchkin?

Is a naturally all "male" species possible?

How can I raise concerns with a new DM about XP splitting?

What was required to accept "troll"?

Did US corporations pay demonstrators in the German demonstrations against article 13?

How to color a zone in Tikz

Invariance of results when scaling explanatory variables in logistic regression, is there a proof?

How to prevent YouTube from showing already watched videos?

Is there a problem with hiding "forgot password" until it's needed?

Superhero words!

A workplace installs custom certificates on personal devices, can this be used to decrypt HTTPS traffic?

Resetting two CD4017 counters simultaneously, only one resets

Bob has never been a M before

Is exact Kanji stroke length important?

How to combine multiple reference table data to json?

How to concatenate text from multiple rows into a single text string in SQL server?How do I format a Microsoft JSON date?How can I pretty-print JSON in a shell script?How to parse JSON in JavaHow can I drop all the tables in a PostgreSQL database?How can I pretty-print JSON using JavaScript?How to parse JSON using Node.js?How do I POST JSON data with Curl from a terminal/commandline to Test Spring REST?How do I get ASP.NET Web API to return JSON instead of XML using Chrome?How do I write JSON data to a file?

0

example:https://rextester.com/CVYPI14438

I got four tables.

The table1 is a reference table for mapping tablename and col.

Table0 is serial table.

Tab77 and roc99 are raw data tables.

table0
 serial
1 a22
2 a33
3 a11


table1
 name tablename col
1 weight tab77 e04
2 height roc99 e09
3 tel tab77 h04


tab77
 serial e04 h04
1 a22 69 9998776
2 a33 50 6668676
3 a11 66 7328989
4 a44 66 7888989


roc99
 serial e09
 1 a11 176
 2 a33 182
 3 a22 166
 4 a88 192

I wnat to make the result like this.

 serial raw
1 a22 "tel": "9998776", "height": "166", "weight": "69"
2 a33 "tel": "6668676", "height": "182", "weight": "50"
3 a11 "tel": "7328989", "height": "176", "weight": "66"

I can't type the tablename and col one by one.
Is it possible to get this result automatically with sql only?
Why the table relation designed like this?
How to call this table relation structure?

sql json postgresql

share|improve this question

edited Mar 8 at 7:36

finkfink

asked Mar 8 at 7:16

finkfinkfinkfink

85211

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That is a really strange data model.
  
  – a_horse_with_no_name
  Mar 8 at 8:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What if there are multiple rows for the same serial e.g. in roc99?
  
  – a_horse_with_no_name
  Mar 8 at 8:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You cannot really do this in SQL. Dynamic table names are not supported. You can do this with some very complicated dynamic SQL, but you should fix the data model.
  
  – Gordon Linoff
  Mar 8 at 12:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @GordonLinoff I'm trying to fix the data model with this query.
  
  – finkfink
  Mar 11 at 3:12
  

  
  
  
  

add a comment | 

0

example:https://rextester.com/CVYPI14438

I got four tables.

The table1 is a reference table for mapping tablename and col.

Table0 is serial table.

Tab77 and roc99 are raw data tables.

table0
 serial
1 a22
2 a33
3 a11


table1
 name tablename col
1 weight tab77 e04
2 height roc99 e09
3 tel tab77 h04


tab77
 serial e04 h04
1 a22 69 9998776
2 a33 50 6668676
3 a11 66 7328989
4 a44 66 7888989


roc99
 serial e09
 1 a11 176
 2 a33 182
 3 a22 166
 4 a88 192

I wnat to make the result like this.

 serial raw
1 a22 "tel": "9998776", "height": "166", "weight": "69"
2 a33 "tel": "6668676", "height": "182", "weight": "50"
3 a11 "tel": "7328989", "height": "176", "weight": "66"

I can't type the tablename and col one by one.
Is it possible to get this result automatically with sql only?
Why the table relation designed like this?
How to call this table relation structure?

sql json postgresql

share|improve this question

edited Mar 8 at 7:36

finkfink

asked Mar 8 at 7:16

finkfinkfinkfink

85211

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That is a really strange data model.
  
  – a_horse_with_no_name
  Mar 8 at 8:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What if there are multiple rows for the same serial e.g. in roc99?
  
  – a_horse_with_no_name
  Mar 8 at 8:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You cannot really do this in SQL. Dynamic table names are not supported. You can do this with some very complicated dynamic SQL, but you should fix the data model.
  
  – Gordon Linoff
  Mar 8 at 12:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @GordonLinoff I'm trying to fix the data model with this query.
  
  – finkfink
  Mar 11 at 3:12
  

  
  
  
  

add a comment | 

example:https://rextester.com/CVYPI14438

I got four tables.

The table1 is a reference table for mapping tablename and col.

Table0 is serial table.

Tab77 and roc99 are raw data tables.

table0
 serial
1 a22
2 a33
3 a11


table1
 name tablename col
1 weight tab77 e04
2 height roc99 e09
3 tel tab77 h04


tab77
 serial e04 h04
1 a22 69 9998776
2 a33 50 6668676
3 a11 66 7328989
4 a44 66 7888989


roc99
 serial e09
 1 a11 176
 2 a33 182
 3 a22 166
 4 a88 192

I wnat to make the result like this.

 serial raw
1 a22 "tel": "9998776", "height": "166", "weight": "69"
2 a33 "tel": "6668676", "height": "182", "weight": "50"
3 a11 "tel": "7328989", "height": "176", "weight": "66"

I can't type the tablename and col one by one.
Is it possible to get this result automatically with sql only?
Why the table relation designed like this?
How to call this table relation structure?

sql json postgresql

share|improve this question

edited Mar 8 at 7:36

finkfink

asked Mar 8 at 7:16

finkfinkfinkfink

85211

table0
 serial
1 a22
2 a33
3 a11


table1
 name tablename col
1 weight tab77 e04
2 height roc99 e09
3 tel tab77 h04


tab77
 serial e04 h04
1 a22 69 9998776
2 a33 50 6668676
3 a11 66 7328989
4 a44 66 7888989


roc99
 serial e09
 1 a11 176
 2 a33 182
 3 a22 166
 4 a88 192

 serial raw
1 a22 "tel": "9998776", "height": "166", "weight": "69"
2 a33 "tel": "6668676", "height": "182", "weight": "50"
3 a11 "tel": "7328989", "height": "176", "weight": "66"

share|improve this question

edited Mar 8 at 7:36

finkfink

asked Mar 8 at 7:16

finkfinkfinkfink

85211

share|improve this question

edited Mar 8 at 7:36

finkfink

asked Mar 8 at 7:16

finkfinkfinkfink

85211

asked Mar 8 at 7:16

finkfinkfinkfink

85211

asked Mar 8 at 7:16

finkfinkfinkfink

85211

1 Answer
1

active

oldest

votes

0

This code will give an out put equivalent to your results,

SELECT t1.serial,concat('','"tel": "',max(h04),'", "height": "',max(e09),'", "weight": "',max(e04),'"') as raw FROM 
tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
group by t1.serial ;

You can try this dynamic query here I'm unpivoting your table and doing string agg, with in the array you need to include the column list.

SELECT serial,concat('',string_agg(val,', '),'') as raw
from
(select serial, concat('"',cat,'": "',val,'"') as val
from(select t1.serial,
unnest(array['tel', 'height', 'weight']) AS cat,
 unnest(array[h04, e09, e04]) AS val
 from tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
order by t1.serial)a)b group by serial ;

share|improve this answer

edited Mar 8 at 8:17

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks.However, you type the name and map the name to the column manually in this query. This doesn't help much.
  
  – finkfink
  Mar 8 at 7:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have more columns
  
  – Ajan Balakumaran
  Mar 8 at 7:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes. There are much more rows than this example.
  
  – finkfink
  Mar 8 at 7:43
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So you require a dynamic query, let me have a look
  
  – Ajan Balakumaran
  Mar 8 at 7:49
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55058452%2fhow-to-combine-multiple-reference-table-data-to-json%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This code will give an out put equivalent to your results,

SELECT t1.serial,concat('','"tel": "',max(h04),'", "height": "',max(e09),'", "weight": "',max(e04),'"') as raw FROM 
tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
group by t1.serial ;

You can try this dynamic query here I'm unpivoting your table and doing string agg, with in the array you need to include the column list.

SELECT serial,concat('',string_agg(val,', '),'') as raw
from
(select serial, concat('"',cat,'": "',val,'"') as val
from(select t1.serial,
unnest(array['tel', 'height', 'weight']) AS cat,
 unnest(array[h04, e09, e04]) AS val
 from tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
order by t1.serial)a)b group by serial ;

share|improve this answer

edited Mar 8 at 8:17

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks.However, you type the name and map the name to the column manually in this query. This doesn't help much.
  
  – finkfink
  Mar 8 at 7:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have more columns
  
  – Ajan Balakumaran
  Mar 8 at 7:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes. There are much more rows than this example.
  
  – finkfink
  Mar 8 at 7:43
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So you require a dynamic query, let me have a look
  
  – Ajan Balakumaran
  Mar 8 at 7:49
  

  
  
  
  

add a comment | 

0

This code will give an out put equivalent to your results,

SELECT t1.serial,concat('','"tel": "',max(h04),'", "height": "',max(e09),'", "weight": "',max(e04),'"') as raw FROM 
tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
group by t1.serial ;

You can try this dynamic query here I'm unpivoting your table and doing string agg, with in the array you need to include the column list.

SELECT serial,concat('',string_agg(val,', '),'') as raw
from
(select serial, concat('"',cat,'": "',val,'"') as val
from(select t1.serial,
unnest(array['tel', 'height', 'weight']) AS cat,
 unnest(array[h04, e09, e04]) AS val
 from tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
order by t1.serial)a)b group by serial ;

share|improve this answer

edited Mar 8 at 8:17

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks.However, you type the name and map the name to the column manually in this query. This doesn't help much.
  
  – finkfink
  Mar 8 at 7:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have more columns
  
  – Ajan Balakumaran
  Mar 8 at 7:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes. There are much more rows than this example.
  
  – finkfink
  Mar 8 at 7:43
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So you require a dynamic query, let me have a look
  
  – Ajan Balakumaran
  Mar 8 at 7:49
  

  
  
  
  

add a comment | 

This code will give an out put equivalent to your results,

SELECT t1.serial,concat('','"tel": "',max(h04),'", "height": "',max(e09),'", "weight": "',max(e04),'"') as raw FROM 
tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
group by t1.serial ;

You can try this dynamic query here I'm unpivoting your table and doing string agg, with in the array you need to include the column list.

SELECT serial,concat('',string_agg(val,', '),'') as raw
from
(select serial, concat('"',cat,'": "',val,'"') as val
from(select t1.serial,
unnest(array['tel', 'height', 'weight']) AS cat,
 unnest(array[h04, e09, e04]) AS val
 from tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
order by t1.serial)a)b group by serial ;

share|improve this answer

edited Mar 8 at 8:17

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

SELECT t1.serial,concat('','"tel": "',max(h04),'", "height": "',max(e09),'", "weight": "',max(e04),'"') as raw FROM 
tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
group by t1.serial ;

SELECT serial,concat('',string_agg(val,', '),'') as raw
from
(select serial, concat('"',cat,'": "',val,'"') as val
from(select t1.serial,
unnest(array['tel', 'height', 'weight']) AS cat,
 unnest(array[h04, e09, e04]) AS val
 from tab77 t1
INNER JOIN roc99 t2 on t1.serial=t2.serial
order by t1.serial)a)b group by serial ;

share|improve this answer

edited Mar 8 at 8:17

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412

answered Mar 8 at 7:36

Ajan BalakumaranAjan Balakumaran

1,057412