Transposing Rows to Columns

[geekmachine811]

Hi,

I would like to transpose some data. However, I am having challenges. Probably my approach is wrong. I am connected to a database that in my opinion was "poorly designed".

On an Excel sheet, data is in the format below:

We can clearly see, the 3 columns (C, D & E). 

However, on one of the tables, all these records have been mashed up to one column "Value".

Is there a workaround ?

Below is my sql to the db.

[geekmachine811]

@Anonymous  Thanks. Find attached an Excel sample image. The first table is how the data currently seats. The second table is how i want it to reflect on BI. I am also attaching an image of the current relationship.

[Anonymous]

Hi @geekmachine811 ,

About transposing rows to columns, you can try pivotting column. Below is the example.

Sample data

The Attribute column should change to the column name, and the Value column should be the value in the attribute column.

Select the Attribute column and pivot as follows.

And then if you want to transform columns into attribute-value pairs, where columns become rows again, in addition to deleting existing steps, you can also unpivot columns. Select three columns and click "Unpivot Columns" under Transform.

It's still not clear what “segregate this as one record based on the first colmun "id"” in your new reply means.

In my current opinion, this is the Unpivot Column operation. If possible, please provide some sample data, as well as the expected results. Take care to protect your privacy.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[geekmachine811]

I have managed to create each column by matching with its "id_form_field". Remaining task is, how do i segregate this as one record based on the first colmun "id"