Concatenate title of events, with more values in one cell: according to declare logical series-CodePudding

I hope i don't disturb! Could You help me please in the following MS SQM topic? I accepted with pleasure any advice and idea! How to i create the appropriate query for the the following result? (The pictures and the demand/expected form a bit later.) That i made, and works well, but not the totally expected:

SELECT date AS 'year_mont', 'sell :' ' ' sell ' ' 'buy :' ' ' buy AS 'top_event_summary' FROM analyst_sql_data1 order by date ASC

And the date formatting doesn't works like than expected, neither the DATAPART, or FORMAT, in date, or string data.

The format should be: ‘YYYY-MM’.

And [top_event_summary] format should be: “[event1] : [value] *** [event2] : [value]” Important: events within each record need to be sorted in the order of associated values in a descending order!

So, if i think well, i need to use also the „DESC” function?

Thank You so much indeed in advance for any advice and idea!

Some example datas, to show what is the dataset: Separeted by semicolumn: date;sell;buy 2019-01-01;5588.789643;-5458.217195 2019-01-02;8985.326588;-4932.490439 2019-01-03;4639.63471;-7242.122418 2021-05-10;2395.490115;-7367.779425 2021-05-11;6580.783202;-8198.148223 2021-05-12;5403.18012;-8477.97577 2021-05-13;5082.529925;-2984.220069

It is works, but not totally provide the required result:

SELECT date AS 'year_mont', 'sell :' ' ' sell ' ' 'buy :' ' ' buy AS 'top_event_summary' FROM analyst_sql_data1 order by date ASC

And i would like to ask: why doesn't works it with the absolute value: 'sell :' ' ' sell ' ' 'buy :' ' ' ABS(buy) AS 'top_event_summary'

And also these aren't works well:

But it is works, but i couldn't make any additional function embed/nested in these case? „('sell :' ' ' sell) ' ' ('buy :' ' ' buy AS 'top_event_summary)'”. The main target: How could i create the required query for these result?

.-.-.-.- The accepted form is: [year_month] format has to be ‘YYYY-MM’

But, unfortunately: couldn't make the appropriate format, i tried it across these ways: But in case of: SELECT FORMAT(date, 'yyyy-mm') FROM analyst_sql_data

The all month are appeared only: "00" like this.

And these don't work either:

SELECT date DATEPART(yyyy, mm,) FROM analyst_sql_data

These also doesn’t work.

SELECT FORMAT(date, 'yyyy-mm') AS 'date', 'sell :' ' ' sell ' ' 'buy :' ' ' buy AS 'top_event_summary' FROM analyst_sql_data

And the DATAPATR , don't writes the right format 01, only 1 at every each month. -- SELECT DATEPART(year, date) '-' DATEPART(month, date) AS 'year_month' FROM analyst_sql_data

SELECT DATEPART(year, date), '-' DATEPART(FORMAT(date, 'mm') date) FROManalyst_sql_data

SELECT DATEPART(year, month, date FROM analyst_sql_data

How could i write the expected query that provides the same result, like in the attached picture.

And how could i use and concatenate and nested/embedded functions: like GROUP BY, ABS, MAX: (local MAX according to in every each month/MONTH), and add defined expressions 'event1' contacted to sell and 'event2' contacted to buy. Like this:

“[event1] : [value] *** [event2] : [value]” In the right format.

Thank You so much indeed for any advice, or indeed in advance!

CodePudding user response：

I assume that it should work like this:

SELECT date_format(date,'%Y - %c') as customDateFormat, case when abs(buy)>abs(sell) then concat('buy:',abs(round(buy,2)),' *** sell:',abs(round(sell,2))) else concat('sell:',abs(round(sell,2)),' *** buy:',abs(round(buy,2))) end as top_event_summary from test;

test is a name of the table where entities are saved