SQL Pivot EAV - Printable Version

SQL Pivot EAV - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: SQL Pivot EAV (/thread-22818.html)

SQL Pivot EAV - Quentin - Nov-28-2019

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

RE: SQL Pivot EAV - buran - Nov-28-2019

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

for example, using pandas

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

RE: SQL Pivot EAV - Quentin - Dec-03-2019

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