CODEWITHSUNDEEP
azure-log-analyticsazure-data-explorerkql
sherifffruitfly
sherifffruitfly

Reputation: 465

Kusto: How to unpivot - turn columns into rows?

Using the StormEvents table on the Samples database on the help cluster:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect

I would like row-based output of the form:

desired un-pivoted output

I see the pivot() function, but it appears to only go the other direction, from rows to columns.

I've been trying various pack() ideas, but can't seem to get the required output.

Example:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| extend Packed =   pack(
                    "CasualtyType", "InjuriesDirect", "CasualtyCount", InjuriesDirect,
                    "CasualtyType", "InjuriesIndirect", "CasualtyCount", InjuriesIndirect,
                    "CasualtyType", "DeathsDirect", "CasualtyCount", DeathsDirect,
                    "CasualtyType", "DeathsIndirect", "CasualtyCount", DeathsIndirect
                )
| project-away InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| mv-expand Packed

This gives me too many rows, and it's not clear to me how to convert them to columns anyway.

What's a correct pattern to use for the required output?

Upvotes: 3

Views: 9487

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could try something along the following lines:

let casualty_types = dynamic(["InjuriesDirect", "DeathsDirect", "InjuriesIndirect", "DeathsIndirect"]);
StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, properties = pack_all()
| mv-apply casualty_type = casualty_types to typeof(string) on (
    project casualty_type, casualty_count = tolong(properties[casualty_type])
)
| project-away properties

Upvotes: 9

Related Questions