I’m using hook_views_data to add a custom table and its fields to Views. The table relates to nodes, and looks like this:

+ node id   | my_value   | time_period  + |-----------|------------|--------------| | 1         | 100        | 1 year       | | 1         | 200        | 1 month      | | 2         | 300        | 1 year       | | 2         | 400        | 1 month      | +---------------------------------------+ 

I have defined an implicit relationship to my custom table, so I have a view with nodes as the base table and I can access the my_value field from my custom table.

The time_period column is new, however. Now there are multiple rows per node, with different time_period values, and each of these results in a different output row in my view.

Rather than having a my_value field for each row, I would like to have my_value (1 year) and my_value (1 month) fields, so that each output row represents one node – i.e. rows for the same node are collapsed and their fields are separated.

I’ve cobbled together the SQL that achieves this, but I can’t figure out how to accomplish this with hook_views_data:

SELECT      node.title AS node_title,      node.nid AS nid,      custom_table_1month.my_value AS my_value_1month,     custom_table_1year.views AS my_value_1year FROM      node  LEFT JOIN     custom_table custom_table_1month ON custom_table_1month.node_id = node.nid AND custom_table_1month.time_period = '1 month' LEFT JOIN     custom_table custom_table_1year ON custom_table_1year.node_id = node.nid AND custom_table_1year.time_period = '1 year' 

I have tried to add two base tables to views, called custom_table_1month and custom_table_1year, but these value names are used verbatim as the table name whereas I really want them as aliases, so Views complains that these tables don’t exist, and I can’t seem to specify the correct table name anywhere.

I’m feeling like this has to be possible, because I’ve got the SQL for it! I just can’t for the life of me figure out how to do this with Views.