行转列类型一：数据分组后进行行转列

--行转列类型一：数据分组后进行行转列(每列的值相同的方式)
--预期结果：ID和Name同一人唯一，分组后，显示出相同记录下的分组人名
create table tmp
(  
  id char(5), 
  name char(20),  
  value Integer 
)

Insert Into tmp(Id,Name,Value) Values('001','张三','1000') ;
Insert Into tmp(Id,Name,Value) Values('001','张三','2000') ;
Insert Into tmp(Id,Name,Value) Values('001','张三','3000') ;
Insert Into tmp(Id,Name,Value) Values('002','李四','1000') ;
Insert Into tmp(Id,Name,Value) Values('002','李四','2000') ;

Select * From tmp   

SELECT id, name
    ,isnull(MAX(CASE value WHEN 'aaaa' THEN value END),'') AS value1
    ,isnull(MAX(CASE value WHEN 'bbbb' THEN value END),'') AS value2
    ,isnull(MAX(CASE value WHEN 'cccc' THEN value END),'') AS value3
FROM tmp
GROUP BY id, name order by Id ;


Select Id,Name 
    ,Max(Case Value When 1000 Then Value End) As value1
    ,Max(Case Value When 2000 Then Value End) As Value2
    ,Max(Case Value When 3000 Then Value End) As value3
From tmp 
Group By Id,Name Order By Id ;

------------------------------------------------------------------------------------