Clever Geek Handbook

Concatenate / Concatenate in SQL

I am trying to reorder / group a result set using SQL. I have several fields (which have been renamed to something less specific for the sake of example), and each logical group of records has a field that remains constant - an address field. There are also fields that are present for each address, they are the same for each address.

id  forename    surname     address
1   John        These       Address1
2   Lucy        Values      Address1
3   Jenny       Are         Address1
4   John        All         Address2
5   Lucy        Totally     Address2
6   Jenny       Different   Address2
7   Steve       And         Address2
8   Richard     Blah        Address2

address     John    Lucy            Jenny       Steve       Richard
Address1        These   Values          Are         (null)      (null)
Address2        All     Totally         Different   And         Blah

For example: John, Lucy, Jenny, Steve and Richard are the only possible names for each address. I know this because it is stored elsewhere.

Can I select values ​​from the actual entries in the left hand image and return them as a result set similar to the one on the right? I am using MySQL if it matters.

0


source to share


3 answers


Assuming the column headings are "john", "lucy", etc. fixed, you can group by address field and use if () functions in combination with aggregate operators to get your results:

select max(if(forename='john',surname,null)) as john,
       max(if(forename='lucy',surname,null)) as lucy,
       max(if(forename='jenny',surname,null)) as jenny,       
       max(if(forename='steve',surname,null)) as steve,       
       max(if(forename='richard',surname,null)) as richard,
       address
from tablename 
group by address;

It's a little flimsy though.



There is also a group_concat function that can be used (within the constraints) to do something like this, but it will be ordered by row, not by column as you think.

eg.

select address, group_concat( concat( forename, surname ) ) tenants 
from tablename
group by address;
+1


source


I'm not sure, but I think you are trying to do this GROUP BY.

SELECT Address,Name FROM Table GROUP BY Name


if you want to select more columns, make sure they are included in the GROUP BY clause. In addition, you can now perform aggregate functions such as MAX () or COUNT ().

0


source


I'm not sure about the question, but from what I understand you can do:

SELECT concat(column1,column2,column3) as main_column, address from table;
0


source






More articles:


All Articles