Merging rows of the same value together

Question

I have a ODBC connection where I execute a query in PHP. 5 tables are joined together to get the data I need and only 4 columns are used or "SELECTED" of 95 total columns.

The problem is that I get same "usernames" and "management" multiple times because each user have multiple data in the "permType" column and other columns that I'm not using at this point. The code itself works, the only problem is the column thing..

It is a SQL Server, using Microsoft SQL Server Management Studio, 2008 R2

I have tried to group them using "GROUP BY" in SQL but I get an error:

Column "ID" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have googled this error but I couldn't get it to work from the answers I found..

Feels like I've tried everything I found on google/stackoverflow.

The table looks something like this right now:

Name | Management | PermType

But i want it to look like this:

Name | Management | PermType

John Doe | Orderperm | PC perm, Telephone perm, Permission Perm
Ash Ketcu| SomeOrder | PC perm, Telephone perm, Permission Perm

Here's some of the code:


**//I do odbc fetch, and echo out a table:**

while ($row = odbc_fetch_array($result)) {
 echo "<tr>";
    echo "<td>" . utf8_encode($row[$Firstname]) . " " . utf8_encode($row[$Lastname]) . "</td>";
    echo "<td>" . utf8_encode($row[$Management]) . "</td>";
    echo "<td>" . utf8_encode($row[$PermType] ). "</td>";
 echo "<tr>";
}


//The SQL:

SELECT
PT.NAME AS PERMTYPE,
R.MANAGE AS MANAGEMENT,
R.FIRST_NAME AS FIRSTNAME,
R.LAST_NAME AS LASTNAME

FROM WEBORDER_PRODUCTTYPESLINKS PL
INNER JOIN WEBORDER_PRODUCTTYPES PT ON PT.ID = PL.IDPRODUCTTYPE
INNER JOIN WEBORDER_INDIVIDUALPERM_TYPES IT ON IT.IDPRODUCTTYPE = PT.ID
INNER JOIN C_CONTACT_PERSONS R ON R.MASTER_ID = IT.IDMANAGER AND (R.CODE = 1)
where R.MANAGEMENT <> 'NULL' and PT.ID between 65 and 72

Maybe there is a sulution in PHP instead of trying to group them in SQL and then presenting the data in PHP?

I'm happy for any suggestions!

UPDATE

Managed to make some progress, the only problem now is that same value in the column "PermType" is showing like 100 times. Can't really understand what exactly i'm doing wrong here..

This is what my table looks like right now:
+-----------+------------+-----------+---------------------------------+
| FIRSTNAME | LASTNAME   | MANAGEMENT| PermType                        |
+-----------+------------+-----------+---------------------------------+
| Ash Ketcu | SomeOrder  | SomeOrder | PC perm, PC permPC, PC permPC...|
| John Doe  | Orderperm  | Orderperm | PC perm, PC permPC, PC permPC...|
+-----------+------------+---------------------------------------------+

SELECT
R.FIRST_NAME,
R.LAST_NAME,
R.MANAGEMENT,
PERMTYPE = 
    (
    SELECT ', ' + PT.NAME
        FROM WEBORDER_PRODUCTTYPESLINKS PL
            INNER JOIN WEBORDER_PRODUCTTYPES PT ON PT.ID = PL.IDPRODUCTTYPE
            INNER JOIN WEBORDER_INDIVIDUALPERM_TYPES IT ON IT.IDPRODUCTTYPE = PT.ID
            INNER JOIN C_CONTACT_PERSONS R ON R.MASTER_ID = IT.IDMANAGER 
        WHERE PT.ID BETWEEN 65 AND 72
        FOR XML PATH('')
        )

FROM C_CONTACT_PERSONS R
WHERE R.MANAGEMENT <> 'NULL'

I'm thankful for any input :)!

What database is this? Is it `mysql`, `postgresql`, `sql-server`, `oracle`, some other product? Depending on the RDBMS this would just require wrapping that last column in a `group_concat()` or `list_agg()` type function and putting every column in your GROUP BY clause. — JNevill, Sep 23 '19 at 13:47
Most database systems have some form of a list aggregator. For example, you can use listagg in Oracle to aggregate nongrouped values into a single list field. — kainaw, Sep 23 '19 at 13:52
This could help you out: https://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value — Stivan, Sep 23 '19 at 13:57
Forgot to say it is a SQL Server, using Microsoft SQL Server Management Studio, 2008 R2. And yes, there are some null as value, it's a pretty old databse and i did'nt create it :). — JelloElks, Sep 23 '19 at 14:28
Possible duplicate of [Selecting rows as columns in mysql through php](https://stackoverflow.com/questions/13689577/selecting-rows-as-columns-in-mysql-through-php) — alexherm, Sep 23 '19 at 15:17

score 1 · Answer 1 · answered Sep 23 '19 at 14:18

You could do this entirely with MySQL like this:

CREATE TABLE group_test (Name VARCHAR(255), Management VARCHAR(255), PermType VARCHAR(255));

INSERT INTO group_test (Name, Management, PermType) VALUES
("John Doe","Orderperm","PC perm"),
("John Doe","Orderperm","Telephone perm"),
("John Doe","Orderperm","Permission Perm"),
("Ash Ketcu","SomeOrder","PC perm"),
("Ash Ketcu","SomeOrder","Telephone perm"),
("Ash Ketcu","SomeOrder","Permission Perm");

SELECT Name, Management, GROUP_CONCAT(PermType) FROM group_test GROUP BY Name, Management;

+-----------+------------+----------------------------------------+
| Name      | Management | GROUP_CONCAT(PermType)                 |
+-----------+------------+----------------------------------------+
| Ash Ketcu | SomeOrder  | PC perm,Telephone perm,Permission Perm |
| John Doe  | Orderperm  | PC perm,Telephone perm,Permission Perm |
+-----------+------------+----------------------------------------+
2 rows in set (0,00 sec)

Well, since the data changes quite often i will have to make some kind of procedure that copies the data sometimes a week or something? But yeah, i could actually try that. — JelloElks, Sep 23 '19 at 14:31
@JelloElks The point of the answer is the GROUP_CONCAT function. I mentioned LISTAGG for Oracle in the comments. You are asking for a funciton that aggregates or concatenates multiple values. In MSSQL, it is STRING_AGG. — kainaw, Sep 23 '19 at 18:13
Sorry missed that.. Well, since its mssql 2008, there are no such functions as GROUP_CONCAT and STRING_AGG. The only way to achive something like that functions, is maybe using "STUFF" together with "FOR XML PATH" i guess. — JelloElks, Sep 24 '19 at 06:28

Merging rows of the same value together

Name | Management | PermType

Name | Management | PermType

1 Answers1