how to convert the rows into columns in Azure Data Explorer (Kusto)

Question

I have a situation where i need to convert the rows into columns in Kusto

let mainTable = datatable (GroupName:string, MemberCount:int, MemberType:string)[
"ABC",5,"User",
"ABC",10,"MSI",
"ABC",15,"ServiceAccount",
"ABC",20,"SPN",
"XYZ",3,"User",
"XYZ",6,"MSI",
"XYZ",9,"ServiceAccount",
"XYZ",12,"SPN",
];
mainTable

Above mainTable results us with 8 rows. But expected result should have only 2 rows for 2 GroupName(s) with 6 Columns. Need to convert all the MemberType to columns. GroupName, MemberCount, MSI, User, SPN, ServiceAccount

I tried | evaluate pivot(MemberType) but it is still giving me 8 rows.

Answer 1

I didn't find the definition of your expected output clear enough. It may have helped if you would have provided it in tabular format.

That said, you may find the following helpful - it uses:

• the bag_pack() function
• the make_bag() aggregation function
• the bag_unpack() plugin

datatable (GroupName: string, MemberCount: int, MemberType: string)
[
    "ABC", 5, "User",
    "ABC", 10, "MSI",
    "ABC", 15, "ServiceAccount",
    "ABC", 20, "SPN",
    "XYZ", 3, "User",
    "XYZ", 6, "MSI",
    "XYZ", 9, "ServiceAccount",
    "XYZ", 12, "SPN",
]
| summarize b = make_bag(bag_pack(MemberType, MemberCount)) by GroupName
| evaluate bag_unpack(b)

GroupName	MSI	ServiceAccount	SPN	User
ABC	10	15	20	5
XYZ	6	9	12	3

Alternatively, you can use the pivot plugin:

datatable (GroupName: string, MemberCount: int, MemberType: string)
[
    "ABC", 5, "User",
    "ABC", 10, "MSI",
    "ABC", 15, "ServiceAccount",
    "ABC", 20, "SPN",
    "XYZ", 3, "User",
    "XYZ", 6, "MSI",
    "XYZ", 9, "ServiceAccount",
    "XYZ", 12, "SPN",
]
| evaluate pivot(MemberType, sum(MemberCount))

GroupName	MSI	ServiceAccount	SPN	User
ABC	10	15	20	5
XYZ	6	9	12	3