Grouping rows of a column and splitting the related columns into new ones

[Anonymous]

Hi,

I tried for a while to solve this but can't work it out. A lot of the suggestions on here are similar but when I've followed solutions it never quite worked out and it's ended up duplicating my rows.

I have a table called Tickets which contains support ticket numbers. Each ticket number has an ID column. This column and table is linked to another one called Dynamic Fields Values. The link to the Ticket table is on a column called object_id and in this there are multiple duplicate entries linking to the tickets. For each object_ID there is a column called dynamic_field.name and a corresponding column called value_text. This mean that for example object_id 4700 could have a dynamic_field.name of 'Source' with the value_text 'Email' and then in another row for 4700 a dynamic_field.name of 'OverSLA' with the value_text 'Yes'.

What I want is one row for 4700 with a column for Source and a column for OverSLA. Is this possible? nothing I've tried has worked so far and when I try to do a join between Tickets and DynamicFields I end up with duplicate ticket numbers. Please help!

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file, all you need to do is Pivot dynamic_field.name column as below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Anonymous 

Please can you provide a usable data sample, and if possible can you present an outcome that you desire?

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

Hi,

This is the current table structure.

object_id	value_text	dynamic_field.name
4762	Phone	Source
4762	2 medium	ITSMCriticality
4762	3 low	ITSMImpact
4762	3rd Party Referral	ClosureActions
35934	No	ITSMReviewRequired
35934	Complex	Screen
35934	0012000000xb1sC	SalesID
35934	Phone	Source

I need to separate out the dynamic_field.names into new columns and then the add the corresponding value_text for each grouped object_id. I need to do this so I have a one to one relationship between the object_id and another table with the ticket data.

object_id	Source	ITSMCriticality	ITSMImpact	ClosureActions	ITSMReviewRequired	Screen	SalesID
4762	Email	2 medium	3 low	3rd Party Referral
35934	Phone				No	Complex	0012000000xb1sC

As added background the Ticket table is the main one I'm reporting on so I would like to be able to create reports for the dynamic fields for each ticket. I can do this once I manage to get my table to look like the one above.

Make sense?

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file, all you need to do is Pivot dynamic_field.name column as below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi,

That's great and solves one part but then I need to group by object_id. I tried that but end up with the below. When I expand count to show the rows it just ungroups the object_id again.

Thanks,

Tom

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Only select this column "dynamic_field.name", then pivot column

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @Anonymous 

I've used Pivot Column in my solution, you don't need to use Group By or else, have I missed something?

Best Regards,
Mariusz

Please feel free to connect with me.