How to merge tables and delete duplicates from table cells?

[Charfi Ouacef]

Hello together,

I would like to merge these two tables from different pages:

to this final table:

My problem currently is that i have no idea how to remove the duplicate values.

Can somebody please help me?

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hi @Charfi Ouacef,

Please try the following SQL query for the case:



SELECT
FORMATWIKI(SUM('male' + "\n")) AS 'male',
FORMATWIKI(SUM(DISTINCT('beard') + "\n")) AS 'beard',
FORMATWIKI(SUM(DISTINCT('shoe size') + "\n")) AS 'shoe size'
FROM (SELECT * FROM T1 OUTER JOIN T2 ON T1.'male' = T2.'male')



Seems it can help your case.

[Charfi Ouacef]

Thats exactly what I was looking for. Thank you very much!

[Charfi Ouacef]

Hi @Katerina Rudkovskaya _Stiltsoft_ ,

just one little question. If I have more information in a cell, then the words are simply lined up.

SELECT
FORMATWIKI(SUM('male' + "\n")) AS 'male',
FORMATWIKI(SUM(DISTINCT('beard') + "\n")) AS 'beard',
FORMATWIKI(SUM(DISTINCT('shoe size') + "\n")) AS 'shoe size'
FROM (SELECT * FROM T1 OUTER JOIN T2 ON T1.'male' = T2.'male')

Is there a way to separate the words to merge them like discussed? I also have the possibility to change the original tables but i don't want to create a row for every Information in the cell.

Best regards
Ouacef

[Stiltsoft support]

The contents of the specific cell can't be treated separately.

If "John" has

yes(enter)

maybe(enter)

no

in his cell, then all the cells' contents will be compared with the other cells.

So, your result that is

yesmaybeno

is correct.

If such behavior suits you and the question is that the result text is unreadable, you may just use spaces:

yes (enter)

maybe (enter)

no

Then you'll get 

yes maybe no

as a result.

If you need to compare items separately, you indeed need to create several rows for "John". And don't forget to use the DISTINCT function for 'male' column, otherwise you'll get several "Johns".

[Fabio Racobaldo _Herzum_]

Hi @Charfi Ouacef and welcome,

tables in Confluence are not structured items so you should do that manually. There are not builtin features to do that.

Fabio

[Charfi Ouacef]

Hi @Fabio Racobaldo _Herzum_ ,

thank you very much for the fast answer. Is there no possibility to solve the problem with table transformation and SQL?

[Fabio Racobaldo _Herzum_]

hey @Charfi Ouacef ,

tables are stored as text in Confuence DB so you can't manage merge from text using SQL query.

Fabio

[Charfi Ouacef]

Hi @Fabio Racobaldo _Herzum_ ,

In sql it is possible to join the same text in cells using "Group By" command. Is there no possibility to use another operator, something like FORMATWIKI(SUM...) or "Select Distinct"??

@Katerina Kovriga _Stiltsoft_ do you maybe have a solution for the problem?

Best regards
Ouacef