Table Transform: Separating Row Data Into Specific Columns

[Cliff Wilson]

Hi All,

I'm admittedly a bit of a novice to SQL Query formatting with the table transform macro by Stiltsoft, and my google-fu hasn't turned up any viable solutions. So I'm hoping you fine folks can help!

Goal:
Take data from Table 2 (Event) and list it out using the values (Month) from table 1 as the column headers, listing out events relevant to the month in the header below. I've included an image of what the result (Table 3) should look like.

I've tried a few different ways to accomplish this, but have so far been unsuccessful in getting close to the desired result. I'm also not even certain that Table 1 is even necessary.

Any help would be greatly appreciated!

[Natalie Paramonova _Stiltsoft_ · Accepted solution]

Hi @Cliff Wilson

We can suggest using the following options to get the table that is rather close to the desired variant.

Option 1: Pivot Table + Table Transformer

Here is what I have on my page in the Edit mode:

Here is how the settings of the Pivot Table macro look like:

The Table Transformer macro is used to hide the unwanted columns and rename the required ones:

SELECT T1.'Literal of Event Jan' AS 'Jan',
T1.'Literal of Event Feb' AS 'Feb',
T1.'Literal of Event Mar' AS 'Mar'
FROM T*

Option 2: Table Transformer

Here is what I have on my page in the Edit mode:

And here is the required SQL query:

SELECT 'Month', 'Event'
FROM (SELECT T1.'Month', CONCAT_VIEW_AGGR(FORMATWIKI(T1.'Event', "\n")) AS 'Event' FROM T1 GROUP BY T1.'Month')
PIVOT (FIRST('Event') for 'Month')

Hope this helps your case.

[Cliff Wilson]

Genuinely want to thank you for taking the time to help me with this Natalie!

Apologies for the delayed response, the usual holiday season rush has gone into full effect for us haha.

I will check it out in more detail later this week, but my initial test page looks like this will do the trick nicely.