SQL Server SQL cross apply and unpivot function

kyks70gy  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(122)

I have a some data regarding the customer service calls and now I want to create a sankey chart with the data in power bi. I need a query to display the customer service flow with a count in sql query.

I need a idea to implement the concept like this
| column1 | column2 | count |
| ------------ | ------------ | ------------ |
| language | mainmenu | 170 |
| mainmenu | exit | 15 |
| mainmenu | customer service | 155 |
| customer service | connected | 140 |
| customer service | exit | 15 |

this is was my sample data i have this in pivot format i want to know how to build this logic in SQL

wh6knrhe

wh6knrhe1#

It could be better if you provided an example of your pivoted data. If it looks like my example in #temp table, you can use unpivoting function.

create table #temp(column1 varchar(255), [mainmenu] int, [customer service] int, [connected] int, [exit] int)

insert into #temp
values
('language',170,0,0,0),
('mainmenu',0,155,0,15),
('customer service',0,0,140,15)

select column1, column2, [count]
from (
    select column1,[mainmenu],[customer service],[connected],[exit]
    from #temp
) as pvt
unpivot(
    [count] for column2 in ([mainmenu],[customer service],[connected],[exit])
) as unpvt
where [count] <> 0 --it's for removing zero values in the output

[

相关问题