Unpivoting the Data in Azure Data Explorer

ADX 156 Reputation points
2021-11-02T12:16:56.273+00:00

Hi I have a data like mentioned on below

Date,Col1,Col2
2021-11-02,a,b
2021-11-02,c,d

Unpivoted data should look like mentioned below.

Date,ColumnName,ColumnValue
2021-11-02,Col1,a
2021-11-02,Col1,b
021-11-02,Col2,c
2021-11-02,Col2,d

The above functionality how we can achieve in Azure Data Explorer.

Important Note: Sometimes the number of columns may be high or sometimes its less
for example one day it is Date,Col1,Col2 another day Date,Col1,Col2,Col3,Col4 and another day Date,Col1,Col2,Col3 like this.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
247 questions
No comments
{count} votes

Accepted answer
  1. BhargaviAnnadevara-MSFT 5,336 Reputation points
    2021-11-05T15:49:09.61+00:00

    @ADX You can modify the query provided earlier as follows:

       TestTable  
       | extend p = pack_all()  
       | project Date, colData = bag_remove_keys(p, dynamic(['Date']))  
       | mv-expand colData  
       | mv-apply colData on (  
           extend ColumnName = tostring(bag_keys(colData)[0])  
           | project ColumnName, ColumnValue = colData[ColumnName]  
       )  
    

    This would produce the output in the expected format. Hope this helps. Do let us know if you have further questions.

    ----------

    If an answer is helpful, please "Accept answer" and/or "Up-Vote" which might help other community members reading this thread.


1 additional answer

Sort by: Most helpful
  1. BhargaviAnnadevara-MSFT 5,336 Reputation points
    2021-11-03T11:14:12.603+00:00

    @ADX Thanks for reaching out. It would have helped to know the exact schema of your data to know how you are currently accommodating variable number of columns.

    Nonetheless, I've assumed a sample structure and worked up a query as follows:

    Table with sample data:

    146202-image.png

    Query:

       datatable(Date:datetime, colData:dynamic)  
       [  
           '2021-11-02', dynamic({ "Col1": "a", "Col2": "b" }),  
           '2021-11-03', dynamic({ "Col1": "c", "Col2": "d" }),  
           '2021-11-04', dynamic({ "Col1": "e", "Col2": "f", "Col3": "g" }),  
           '2021-11-05', dynamic({ "Col1": "h", "Col2": "i", "Col3": "j", "Col4": "k" }),  
       ]  
       | mv-expand colData  
       | mv-apply colData on (  
           extend ColumnName = tostring(bag_keys(colData)[0])  
           | project ColumnName, ColumnValue = colData[ColumnName]  
       )  
    

    Running the above query as is, you'd see the following output:

    146135-image.png

    Please feel free to adapt this query as per your schema.

    Hope this helps. Do let us know if you have further questions.

    ----------

    If an answer is helpful, please "Accept answer" and/or "Up-Vote" which might help other community members reading this thread.