28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
# File 'lib/activerecord_pg_stuff/relation/pivot.rb', line 28
def pivot(row_id, col_id, val_id)
types_sql = %{ SELECT column_name, data_type FROM information_schema.columns WHERE table_name = #{connection.quote self.table.name} AND column_name IN (#{connection.quote row_id},#{connection.quote val_id}) }
types = connection.select_all types_sql
types = types.to_a.map(&:values).inject({}) do |a, v|
a[v[0]] = v[1]
a
end
row_type = types[row_id.to_s]
val_type = types[val_id.to_s]
cols = connection.select_all self.except(:select).select("DISTINCT #{col_id}").order(col_id).to_sql
cols_list = cols.rows.map(&:first).map do |c|
"#{col_id}_#{c} #{val_type}"
end.join(", ")
rel_1 = connection.quote self.select(row_id, col_id, val_id).order(row_id).to_sql
rel_2 = connection.quote self.except(:select).select("DISTINCT #{col_id}").order(col_id).to_sql
sql = %{ SELECT * FROM crosstab(#{rel_1}, #{rel_2}) AS (row_id #{row_type}, #{cols_list}) }
PivotResult.new cols, connection.select_all(sql)
end
|