Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Help with code in AlaSQL (Table transformer)

Matias G Labrana
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
March 29, 2024

Hi guys I have this code:
SELECT
T1."T",
CASE WHEN T1."Count 2024 Q1" IS NOT NULL THEN T1."Count 2024 Q1" END AS "Q1",
CASE WHEN T1."Count 2024 Q2" IS NOT NULL THEN T1."Count 2024 Q2" END AS "Q2",
CASE WHEN T1."Count 2024 Q3" IS NOT NULL THEN T1."Count 2024 Q3" END AS "Q3",
CASE WHEN T1."Count 2024 Q4" IS NOT NULL THEN T1."Count 2024 Q4" END AS "Q4"
FROM T1
It is a table of 5 columns and 3 rows.
I would like create a new column and in the last 2 rows put a text for example:
New column:
Row 1 -> Tittle Column
Row 2 -> text A
Row 3 -> text B

so finally the main table (T1) will be with 6 columns and 3 rows. Do you know if is possible to make this in AlaSQL?

1 answer

2 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 29, 2024

Hi @Matias G Labrana ,

You may try the following solution:

Fri 10-1.png

In the internal Table Transformer macro you use the following SQL structure:

SELECT ROWNUM() AS 'Rownum', *
FROM
(SELECT * FROM T*)

The internal SELECT part that is (SELECT * FROM T*) in my example stands for your original SQL query where you transform your table.

The external SELECT that is SELECT ROWNUM() AS 'Rownum', * FROM is used to add row numbers to your transformed table.

As a result, you will see smth like this:

Fri 10-2.png

Now we can join this table with an additional mini table using the unique 'Rownum' column:

SELECT 'Column 1', 'Column 2', 'Column 3', 'Additional column'
FROM T1
LEFT JOIN T2 ON T1.'Rownum' = T2.'Rownum'

Fri 10-3.png

Please note that I've taken my screenshots for Data Center where you can nest macros directly. 

For Cloud you need to insert the Table Toolbox macro on the page, wrap your tables into it and then go to the macro settings & create the described structure inside the macro shell.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events