SQL group by without repeating fields same as excel pivot-CodePudding

What I need is the following.

Currently, it's repeating column names with the regular group by and sum.

| column 1    | column2     |  column3 | sum |
|-------------|-------------|----------|-----|
|main product |sub product1 |subsub 1  |  500|
|main product |sub product1 |subsub 2  |  300|
|main product |sub product2 |subsub 1  |  300|

I want to get rid of repeating the same as excel pivot, so below, I need.

| column 1    | column2     |  column3 | sum |
|-------------|-------------|----------|-----|
|main product |sub product1 |subsub 1  |  500|
|main product |             |subsub 2  |  300|
|main product |sub product2 |subsub 1  |  300|

Can someone help me with this?

edit : formatted

CodePudding user response：

We can approximate this behavior with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3) rn
    FROM yourTable
)

SELECT col1, CASE WHEN rn = 1 THEN col2 ELSE '' END AS col2, col3, sum
FROM cte t
ORDER BY col1, t.col2;

CodePudding user response：

You can use row_number() :

select col1, 
       case when row_number() over(partition by col1, col2 order by col3) = 1 then col2 else 0 end as col2, 
       col3, sum
from table t
group by col1, col2, col3;

CodePudding user response：

lovely. I was not expecting an answer that quick. Thank you all