Mysql how to implement the different values in the same field as different columns-CodePudding

How do I use the SQL language to realize the above-mentioned conversion to the table below...
Please try the great god teach a lot of methods have failed,

CodePudding user response:

Written in MSSQL, understanding thought
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [time], [fearture] nvarchar (21), [value] nvarchar (21)) 
Insert # T 
Select 1, N 'A', N 'me' union all 
Select 1, N 'B', N 'is' union all 
Select 1, N 'C', N 'dong' union all 
Select 2, N 'A', N 'British' union all 
Select 2, N 'B', N 'jay' union all 
Select 2, N 'C', N ', '
Go 
- the end of the test data 
SELECT [time], 
MAX (CASE [fearture] 
WHEN the 'A' THEN [value] 
The ELSE NULL 
END) Chinese, 
MAX (CASE [fearture] 
WHEN 'B' THEN [value] 
The ELSE NULL 
END) mathematics, 
MAX (CASE [fearture] 
WHEN the 'C' THEN [value] 
The ELSE NULL 
END) English 
The FROM # T 
GROUP BY [time]

CodePudding user response:

It was the conversion operation a
Transformation of three methods

CodePudding user response:

Has been solved, thank you

CodePudding user response:

Characteristic this line if I have 200 different values, I want to write 200
MAX (CASE... END) statement, simple method, excuse me?

CodePudding user response:

reference 4 floor weixin_44711507 response:

if features a line I have 200 different values, I want to write 200
MAX (CASE... END) statement, simple method, excuse me?

Then use dynamic statement, use # 1 moderator data

 
- test data 
If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [time], [fearture] nvarchar (21), [value] nvarchar (21)) 
Insert # T 
Select 1, N 'A', N 'me' union all 
Select 1, N 'B', N 'is' union all 
Select 1, N 'C', N 'dong' union all 
Select 2, N 'A', N 'British' union all 
Select 2, N 'B', N 'jay' union all 
Select 2, N 'D', N ', '
Go 
- the end of the test data 


DECLARE @ SQL VARCHAR (MAX) 

SELECT @ SQL=ISNULL (@ SQL + ', ', ') + '[]' + fearture + ' '
The FROM (SELECT DISTINCT fearture FROM # T) AS A 

The SET @ SQL='SELECT * FROM # T A 
The PIVOT (MAX (VALUE) FOR fearture (+ SQL + '@')) IN the AS B '

The EXEC (@ SQL)