hana sql,transpose column date value to row - Stack Overflow

admin管理员组

文章数量:1123079

I have a table as below and date value are over the year.

DATE	WEEK	BRAND	VALUE
2024-04-01	Monday	abc	100
2024-04-02	Tuesday	abc	200
2024-04-03	Wednesday	abc	300
2024-04-04	Thursday	abc	800
2024-04-01	Monday	xyz	400
2024-04-02	Tuesday	xyz	200
2024-04-03	Wednesday	xyz	500
2024-04-04	Thursday	xyz	800

I have a table as below and date value are over the year.

DATE	WEEK	BRAND	VALUE
2024-04-01	Monday	abc	100
2024-04-02	Tuesday	abc	200
2024-04-03	Wednesday	abc	300
2024-04-04	Thursday	abc	800
2024-04-01	Monday	xyz	400
2024-04-02	Tuesday	xyz	200
2024-04-03	Wednesday	xyz	500
2024-04-04	Thursday	xyz	800

and would like to transpose the column 'DATE' to row and respective values DATE value is for a month ( 2024-04-01 to 2024-04-30) and vary to year and expected result as below

DATE	2024-04-01	2024-04-02	2024-04-03	2024-04-04
BRAND	Monday	Tuesday	Wednesday	Thursday
abc	100	200	300	800
xyz	400	200	500	800

I tried to pivot and series generate date function using SQL which is not working in Hana BTP database using sql.. Please help me with the query to achieve this as expected.

• sql
• hana

Share Improve this question Follow asked 4 hours ago Ambika ..Ambika .. 111 silver badge22 bronze badges 1

• Can you do it like in MySQL? stackoverflow.com/questions/7674786/… – Barmar Commented 4 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -1

To transpose columns to rows in SQL, you can use the UNPIVOT operator or a combination of SELECT, CASE, and GROUP BY. Here’s a basic example using UNPIVOT:

SELECT worker_name, month, sales
FROM (
    SELECT worker_name, Jan, Feb, Mar
    FROM sales_data
) AS src
UNPIVOT (
    sales FOR month IN (Jan, Feb, Mar)
) AS unpvt;

In this example, the sales_data table has columns for each month, and the UNPIVOT operation transforms those month columns into rows under a new column called month. Alternatively, if your SQL dialect does not support UNPIVOT, you can achieve a similar result using UNION ALL:

SELECT worker_name, 'Jan' AS month, Jan AS sales FROM sales_data
UNION ALL
SELECT worker_name, 'Feb', Feb FROM sales_data
UNION ALL
SELECT worker_name, 'Mar', Mar FROM sales_data;

本文标签： hana sqltranspose column date value to rowStack Overflow