DQL: How can I convert row values to column headings

[HeadScratcher]

I have some DQL that looks at a web servers log files and counts the number of hits on services gives me data in a table like this: 

service_name	status_code	hit_count
checkOut	200	1234
checkOut	404	1

I would prefer to present it like this so that I can apply percentage calculations and categorise the record as being OK or needing action.

service_name	200	404
checkOut	1234	1

Is there a way to do this kind of thing?   

[krzysztof_hoja · Accepted solution]

Unfortunately the only technique I know to get it is via same method you probably saw already:
https://community.dynatrace.com/t5/DQL/DQL-How-to-Flatten-an-Array/m-p/251125/highlight/true#M1073

For your data it would look like:

data 
  record(serviceName="checkOut", status_code=200, hit_count=1234),
  record(serviceName="checkOut", status_code=404, hit_count=1),
  record(serviceName="login", status_code=200, hit_count=4),
  record(serviceName="login", status_code=403, hit_count=1)
| summarize { d=collectArray(array(toString(status_code),hit_count)) }, by: {serviceName}
| fieldsAdd d=toString(d)
| parse d, "'[' KVP{ '[' DQS:key ', ' LONG:value ']' ', '?}:code ']'"
| fieldsFlatten code
| fieldsRemove d, code

Kris

[HeadScratcher]

Thanks Kris, you are right I did read the other thread.   This approach does work fine and is actually quite similar to how I would have approached in in SPL which is where my skillset is coming from.