CODEWITHSUNDEEP
phpsqlsql-server
fdsa
fdsa

Reputation: 249

php/sql: Grouping rows based on multiple columns

I am using MSSQL and I have a table that looks like this:

table1 

id |  name  | description | apply start | apply end 
1  | 100-A  |    desc1    |    start1   |    end1     
2  | 100-B  |    desc1    |    start1   |    end1  
3  | 100-C  |    desc1    |    start1   |    end1
4  | 200-H  |    desc2    |    start2   |    end2
5  | 300-B  |    desc3    |    start3   |    end3
6  | 300-C  |    desc3    |    start3   |    end3

I'm trying to output those values from my database into a table using php so it looks like this:

 Name  |   Description   |  Starting From  |  End
----------------------------------------------------
|100-A |                 |                 |       |
|100-B |      desc1      |     start1      |  end1 |
|100-C |                 |                 |       |
|--------------------------------------------------
|200-H |      desc2      |     start2      |  end2 |
|--------------------------------------------------
|300-B |                 |                 |       |
|300-C |      desc3      |     start3      |  end3 |
|--------------------------------------------------

Is there a way to group the description, apply_start, and apply_end columns if all their values are identical into a single row?

Upvotes: 0

Views: 61

Answers (3)

Chris
Chris

Reputation: 2300

Here's the basic idea of how I would do it in PHP (assuming no null characters are going to appear in your description, apply_start and apply_end columns).

// Assuming a result in this format
$result = array(
    array("id" => "1",
    "name" => "100-A",
    "description" => "desc1",
    "apply_start" => "start1",
    "apply_end" => "end1"),
. . .
);

foreach ($result as $row) {
    $groups[$row['description'] . "\0" 
          . $row['apply_start'] . "\0" 
          . $row['apply_end']][] = $row['name'];
}

foreach ($groups as $desc => $names) {
    echo implode(',', $names) . ' | ';
    echo implode(' | ', explode("\0", $desc)) . "\n";
}

Output:

100-A,100-B,100-C | desc1 | start1 | end1
200-H | desc2 | start2 | end2
300-B,300-C | desc3 | start3 | end3

Upvotes: 1

ChrisV
ChrisV

Reputation: 1309

As Jaaz Cole said you should really be doing this in PHP and I'm not sure I'm doing you a favor by providing this, but it can be done in SQL if you really insist.

SELECT DISTINCT STUFF((( SELECT ' ' + [Name] FROM table1 t WHERE t.[description] = table1.[description] AND t.[apply start] = table1.[apply start] AND t.[apply end] = table1.[apply end] FOR XML PATH (''), ROOT('x'), TYPE).value('/x[1]', 'VARCHAR(MAX)') ), 1, 1, '') AS [Name], [description] AS [Description], [apply start] AS [Starting From], [apply end] AS [End] FROM table1

Upvotes: 0

Jaaz Cole
Jaaz Cole

Reputation: 3180

Not really, when it comes to row formatting, that's PHP's job. However, you can make your life easier by adding something to the result set:

SELECT [name], [description], [apply start], [apply end]
    , COUNT([NAME])OVER(PARITION BY [description], [apply start], [apply end]) AS RowSpan
FROM table1
ORDER BY [description], [apply start], [apply end], [name]

Now, when constructing the table using PHP, use RowSpan for the grouped columns. You could even use it in your for loop to output the Name into the single cell of the table if you wanted, based on the diagram.

Upvotes: 0

Related Questions