Combining two columns in Views table

Question

I keep on having different requirements and I always get stuck in Views. This time the requirement is to combine the two columns/fields of a views table but display it in different rows.

The eg: fields I currently have: (Below is the added Picture)

The output of the View in Table (Nickname Multivalue separated by comma)

While Putting the Multiple value field in different rows

In the table settings I did combine two fields like

i.e. Name and Nick Name but that only gives result like:

(Here Nick Name is displayed after Name)

But the requirements I need to display is: (Name and Nick Name in Separate Row but at the same column). The Column needs to be sortable like normal Views Table Sort function.

Is there any settings in Views which lets me perform this? Or How can I accomplish this ?

Edit: If this is not possible and anybody could guide me to build custom query for this then I'd be very thankful.

Edit 2: SQL Query from Views Log is: (This query outputs the 2nd Image from above)

SELECT node.title AS node_title, node.nid AS nid, field_data_field_nick_names.delta AS field_data_field_nick_names_delta, field_data_field_nick_names.language AS field_data_field_nick_names_language, field_data_field_nick_names.bundle AS field_data_field_nick_names_bundle, field_data_field_nick_names.field_nick_names_value AS field_data_field_nick_names_field_nick_names_value, field_data_field_nick_names.field_nick_names_format AS field_data_field_nick_names_field_nick_names_format, node.created AS node_created, 'node' AS field_data_field_nick_names_node_entity_type, 'node' AS field_data_field_ages_node_entity_type, 'girls:page' AS view_name
FROM 
{node} node
LEFT JOIN {field_data_field_nick_names} field_data_field_nick_names ON node.nid = field_data_field_nick_names.entity_id AND (field_data_field_nick_names.entity_type = 'node' AND field_data_field_nick_names.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('girlss')) ))
ORDER BY node_created DESC

Answer 1

You've structured your data in such a way that this can't be done with a single SQL query. You'll always be asking the database for two separate things: (1) a list of people based on their real names, and (2) a list based on their nick names.

But it doesn't have to be this way.

The easiest way to accomplish Picture 4, by far, is to always represent a person's "Name" as an additional "Nick Name".

So "Daina" should have 4 Nick Names: "Kristi", "Rita", "Sweetie", and "Daina". Then, all you need to ask the database for is a list of nick names.

This probably isn't the answer you're looking for. But I would argue that it's a more correct way to store your data. Think of the "Nick Name" field not as a list of alternate names, but rather as a list of all names that the person is known as. Based on the requirements of your situation, that's what it sounds like you're really after, anyway. It's a more complete model for your name data.

Answer 2

It would be possible to achieve this with overriding the views template file: views-view-table.tpl.php.

First you need to put multiple values in different rows.

Then in template file you should check for each row if Name is the same with the previous row. If not print the name and the age and add a new row in the code which includes the nickname and the age. If the name is the same with the previous one, then print only the nickname and the age.

I tried to explain it theoretically, but I can help with the implementation if you have difficulties.

Steps:

1. Override the views template: You need to copy the views-view-table.tpl.php file under your theme folder. This file is located under the Views module views/theme/views-view-table.tpl.php.
2. Rename the template file so that it only applies to that specific view: views-view-table--YOUR-VIEW.tpl.php. You should place the machine name of your view separated with 2 dashes.
3. You need to modify the following code block in this template file:

Code

<?php foreach ($rows as $row_count => $row): ?>
  <?php // dpm($row) // You can use dpm() function to print out the contents of $row array. ?>
  <tr <?php if ($row_classes[$row_count]) { print 'class="' . implode(' ', $row_classes[$row_count]) .'"';  } ?>>
    <?php foreach ($row as $field => $content): ?>
      <td <?php if ($field_classes[$field][$row_count]) { print 'class="'. $field_classes[$field][$row_count] . '" '; } ?><?php print drupal_attributes($field_attributes[$field][$row_count]); ?>>
        <?php print $content; ?>
      </td>
    <?php endforeach; ?>
  </tr>
<?php endforeach; ?>

Answer 3

OK, it looks like you are having the three columns combine into one, and then be split into the two you require, and as such are getting arbitrary data. Try going to "Style Settings" with the options you have now, alter the format to "unformatted", and save. Now go back in and reset it to Table. There should be the option to combine columns, and just choose the two that you wish.

I hope this helps.

Answer 4

In Structure -> Views -> settings -> Check on "Show the SQL query". Go to your view, it displays query.

FORMAT - Show:fields, and then use "Global: PHP" for writing custom query. If you not installed use this link, https://drupal.org/project/views_php .

After selecting Global: PHP - You can use the query already shown and then we can do as we like. All above fields should be checked with "Exclude from display" (i.e., Name, age, etc..).

Answer 5

Sorry I don't have a Drupal 7 testbed handy, but if you don't mind the age being grouped together, you can do something like this (screenshots here are in Drupal 6) that I assume is pretty much the same in Drupal 7.

First, off, grab the fields you need, in this case I created a content type called name_nick_age along with three fields nna_name, nna_nick and nna_age and then created a View:

And then inside the Style Settings for the Table, you by default have something like this:

That I changed to this:

The only change is placing the Name in the Nick column. This then created this table:

You gain access to the style settings by clicking on the little gear to the right of Style: Table and while the screen shots for Drupal 7 will be different, I am betting the functionality is in there for this.

Answer 6

   <?php $result = db_query("SELECT node.title AS node_title, node.nid AS nid, field_data_field_nick_names.delta AS field_data_field_nick_names_delta, field_data_field_nick_names.language AS field_data_field_nick_names_language, field_data_field_nick_names.bundle AS field_data_field_nick_names_bundle, field_data_field_nick_names.field_nick_names_value AS field_data_field_nick_names_field_nick_names_value, field_data_field_nick_names.field_nick_names_format AS field_data_field_nick_names_field_nick_names_format, node.created AS node_created, 'node' AS field_data_field_nick_names_node_entity_type, 'node' AS field_data_field_ages_node_entity_type, 'girls:page' AS view_name
    FROM 
    {node} node
    LEFT JOIN {field_data_field_nick_names} field_data_field_nick_names ON node.nid = field_data_field_nick_names.entity_id AND (field_data_field_nick_names.entity_type = 'node' AND field_data_field_nick_names.deleted = '0')
    WHERE (( (node.status = '1') AND (node.type IN  ('girlss')) ))
    ORDER BY node_created DESC");

    $fullname = "";
    $nickname = "";
    foreach($result as $rec)
    {   
        $fullname .= $rec->node_title.",";
        $nickname .= $rec->field_data_field_nick_names_delta.",";
    }
    echo $fullname;
    echo "<br>";
    echo $nickname;
?>

Check if results are displaying or not?