SQL Pivot EAV

[Quentin]

I am very new to Python but I have a programming background.
I can read data from SQL query in EAV format (I am stuck with the data).

HOLEID, PROJECT, name, value
123, RH, a, b
123, RH, x, y
234, RH, a, c

I would like to pivot the data to a flat table and display it as

holeid, project, a, x
123, RH, b, y
234, RH, c, NULL

Is this possible in Python?

Thanks in advance

[buran]

definitely possible. the exact approach may vary - e.g. what packages you use/are ready to use, etc.

---

for example, using pandas

1 2 3 4 5 6

import pandas data = [(123, 'RH', 'a', 'b'), (123, 'RH', 'x', 'y'), (234, 'RH', 'a', 'c')]   df = pandas.DataFrame(data, columns=['HOLEID', 'PROJECT', 'name', 'value']) pivot = df.pivot_table(index=('HOLEID', 'PROJECT'), columns=('name'), values=['value'], aggfunc=lambda x: x) print(pivot)

Output:
name               a    x
HOLEID PROJECT           
123    RH          b    y
234    RH          c  NaN

Of course you can always iterate over data and construct appropriate data structure yourself

[Quentin]

Thanks Buran,

I now have the data but how do I then get it to display as per a normal table so I can write it to an SQL Table.

Current Output:

name a x
HOLEID PROJECT
123 RH b y
234 RH c NaN

Expected Output:

HOLEID PROJECT a x
123 RH b y
234 RH c NaN

Or is that just the way it is displayed and I can then write back to a new SQL Table using a different method?

Regards