SQL cross apply and unpivot function

Question

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

Answer 1

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

[