Help Pivoting Data

user14170 · 2023-08-28T18:50:01+00:00

There was an error rendering this rich post.

I have a file where I want to have each row show me the charges based on either the charge code or charge name. In this example, I used the name.

I have looked at the data online, and in the ELT. The problem with I’m seeing in the ELT is I would have list the name of every code we have in the number 3 position (shown in Yellow below). That would be almost impossible to do, as the charges or code could change each time I run it. Having more or less each time.

Screen Shot 2023-08-28 at 12.47.06 PM.png

Is there a code or statement you an place in number 3 that will name the column the same as the code ( what I have in number 1) ?

There is an excel data table as well. I can send it to whoever ends up helping with this

Find more posts tagged with

Accepted answers

GrantSmith

You can't do a dynamic pivot with Magic ETL however you can leverage prepared statements and SQL in a MySQL dataflow to do dynamic pivoting. You can follow the SQL with something like https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

All comments

MichelleH

@user14170 I don't know of a shortcut for naming the columns off hand. Do the pivoted columns need to be coded into the dataflow or are you able to use a pivot table card? If you have a large amount of charge codes you need to pivot, then having them listed as individual rows may give you more flexibility downstream.

GrantSmith

trafalger

Agreed, I've done dynamic column renaming using a Stored Proc in a MySQL dataflow - you can query the system tables to get the schema.