SQL Server SQL cross apply and unpivot function

kyks70gy 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(111)

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

sql-server

来源：https://stackoverflow.com/questions/76424303/sql-cross-apply-and-unpivot-function

1条答案

按热度按时间

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

[

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server SQL cross apply and unpivot function

1条答案

相关问题

热门标签

最新问答