A data format problem, online, etc., master to help, thank you
Table 1:
ID GroupName attribute OP value
1 ABC Download: = 151-250
2 ABC Upload: = 50-150
3 EFG Download: = 152-250
4 EFG Upload: = 50-150
How to convert the data in the table above into the following format
ID groupname Download Upload
1 ABC 151-250 50-150
2 EFG 152-250 50-150
Sql:
SELECT A.groupname,a.value as upload,b.value as Download from Radgroupreply a left joins Radgroupreply B on A.GROUPNAME=B.G Roupname WHERE a.attribute= ' upload ' and b.attribute= ' download ' and A.groupname in (' abc ', ' EFG ')
This method can be achieved, but the sense of efficiency is a little low, there is no better way?
Using a temporary table is not more efficient than the above method, if you use a temporary table, how to insert data into the temporary table, if directly create and then execute the above SQL, so there is no point, there is no good way to insert it?
Because this table data is relatively large, considering the efficiency problem, want to find a better solution, please all the experts to help, give some ideas, greatly appreciated!
On-line, emergency!!!!!
------Solution--------------------
Create a temporary table with the stored procedure
------Solution--------------------
You are asking for a crosstab, and there are ready-made stored procedures for reference. Search for "cross-table" to find
------Solution--------------------
Row-and-column conversions ...
SQL Code
Set @id = ""; Select (@id: [email protected]+1) as ID, groupname, group_concat (if (attribute= ' Download ', ' value ', ') Separator ") as Download, Group_concat (if (attribute= ' upload ', ' value ', ') separator ') as upload from table name GROUP by group Name
