Reporting - Transpose catalog variable question field as column and variable value in row

[Community Alums]

I'm working on the report to display Catalog Variables along with Task, Item and Request. I've created a database view to join sc_req_item, sc_task and sc_item_option_mtom tables. The report displays all variables with value in the same row. Is it possible to display Question field from 'Variable Ownership' table as columns and 'Value' field in row? 

[Gabriel3]

For Catalog itens and based on the article by @Yvonntje (https://community.servicenow.com/community?id=community_blog&sys_id=e94d2229dbd0dbc01dcaf3231f96190f) you'll need to duplicate the table sc_item_option and sc_item_option_mtom for each variable you want to transform in a column.

In my case I have a simple Catalog item:

So I create 3 joins in sc_item_option and 3 in sc_item_option_mtom, for each variable (named qty, model and stock😞

For each join in the sc_item_option table, the where clause is filtering by that specific question/variable:

For the Quantity variable, for example:

optqty.item_option_new='9ad9848f1b764d109a1b0dcbe54bcb39'

For each join in the sc_item_option_mtom table, the where clause just reference the option created early:

voqty.sc_item_option = optqty.sys_id

And the last join is for the sc_req_item table, that will join all options/questions/variables with the RITM:

voqty.request_item=reqitem.sys_id && vomodel.request_item=reqitem.sys_id && vostock.request_item=reqitem.sys_id

Since I have only one "generic" variable, and 2 variables with Reference type, I also join the table alm_stockroom for the 3rd variable "Stockroom" and cmdb_model for the 1st one "Product Model".

The database view brings me one row for request (RITM) with the values I needed to use in a report:

Here I didn't need to change the name of columns like @Thomas Davis said (https://community.servicenow.com/community?id=community_blog&sys_id=d044d1321bbe1c908672ea89bd4bcb32)

The column Display value was my Product model, Name is my Stockroom and the generic Value will be the Quantity.

For reference, the list columns are:

[Uncle Rob]

There's way easier ways to do this now:

[Nikki N]

How did you join the tables?

[Uncle Rob]

Probably database views.

[Gabriel3]

Yes, like Robert said, is a DB View.

But you join each table sc_item_option, with the sc_item_option_mtom (for each variable).

At the last, you will join all sc_item_option_mtom tables, with sc_req_item:

voqty.request_item=reqitem.sys_id