SQL: Merging two result rows into one

Question

In the database,

Each testcontentelement has many testcontentelementcetpropertyvalue
Each contentelementtypeproperty has many testcontentelementcetpropertyvalue

This query

select tce.id, tce.stem, tce.prompt, tcep.contentelementtypepropertykey, 
cetp.value from testcontentelement tce 
  INNER JOIN testcontentelementcetpropertyvalue tcep
    ON tce.id = tcep.testcontentelementid
  INNER JOIN contentelementtypeproperty cetp
    ON tcep.contentelementtypepropertyid = cetp.id 
  WHERE tcep.contentelementtypepropertykey like 
'%cet_property_cati_msproperties_assessed_category%'and cetp.value NOT LIKE 
'%HI_%' 
  ORDER BY tce.id;

Renders this result:

  id    stem    prompt  contentelementtypepropertykey   value
1392    aaaa    a1      assessed_category_A             Asian Studies
1392    aaaa    a1      assessed_category_B             R01_LIKERT
1397    cccc    c1      assessed_category_B             R01_LIKERT
1397    cccc    c1      assessed_category_A             Biology
1402    eeee    e1      assessed_category_A             Chemistry
1402    eeee    e1      assessed_category_B             R01_LIKERT

But I would prefer to retrieve as follows with each TWO rows merged into one row:

  id    stem    prompt  contentelementtypepropertykey   valueA          ValueB
1392    aaaa    a1      assessed_category_A             Asian Studies    R01_LIKERT

Can someone suggest a way to do this?

select tce.id, tce.stem, tce.prompt, tcep.contentelementtypepropertykey, cetp.value,tcee.value valueB from testcontentelement tce INNER JOIN testcontentelementcetpropertyvalue tcep ON tce.id = tcep.testcontentelementid INNER JOIN contentelementtypeproperty cetp ON tcep.contentelementtypepropertyid = cetp.id INNER JOIN testcontentelement tcee ON tce.id = tcee.id WHERE tcep.contentelementtypepropertykey like '%cet_property_cati_msproperties_assessed_category%'and cetp.value NOT LIKE '%HI_%' ORDER BY tce.id; — Shifat, Sep 24 '17 at 21:06
ERROR: column tcee.value does not exist LINE 1: ..., tcep.contentelementtypepropertykey, cetp.value, tcee.value... — Jake, Sep 24 '17 at 21:21
Your table names are too hard to read. Please shorten them and possibly use underscores or something like camelCase. — Rick James, Sep 25 '17 at 00:37
I think this was a bad question all round. I apologize for posting. The answer linked as duplicate will help me solve the problem — Jake, Sep 25 '17 at 03:07

score 0 · Answer 1 · answered Sep 24 '17 at 21:11

0

Assuming you will have only two rows and rest other cols are going to have identical values,

select tce.id, 
       max(tce.stem), 
       max(tce.prompt), 
       max(tcep.contentelementtypepropertykey), 
       max(cetp.value) valueA, 
       min(cetp.value) valueB 
from testcontentelement tce 
***All your conditions***
group by id

answered Sep 24 '17 at 21:11

Valli

1,440
1
8
13

sorry, no, each two rows will be merged into one row. I wasn't clear – Jake Sep 24 '17 at 21:15
1

So you will have more than two rows for a particular id? if you have three rows for a particular id, first two will be merged. how about the third one? – Valli Sep 24 '17 at 21:17
For now, I will be happy with two. It may be dynamic later, but not essential for now – Jake Sep 24 '17 at 21:22
1

The above query will work if you have two rows for a id. It will merge any number rows for each id, into one row for each id. It will populate the max of value(Asian studies in ur example) in valueA and min of value(R01_LIKERT) in ValueB – Valli Sep 24 '17 at 21:25

score 0 · Answer 2 · answered Sep 24 '17 at 21:23

Are you sure that is the result set of that query?

With the filter you are using on contentelementtypepropertykey it should not be possible.

Also, not sure if you are aware but '_' when used with LIKE is a special character. e.g.

like '%HI_%'

Would return the following results:

CHILL
HIM
HIMSELF
HIT
HITMAN

Basically, any character is accepted instead of the underscore, so long as it is a character.

SQL: Merging two result rows into one

2 Answers2