向大牛们提问了，

向大牛们提问了，求助急……
现在有一表结构为
ID User Date Flow
================================================
id1 u1 d1 f1
id1 u2 d2 f2
id1 u3 d3 f3
id2 u4 d4 f2
id2 u5 d5 f3


其中flow值有（f1,f2,f3）,希望转换成这种格式

ID fl_user f1_date f2_user f2_date f3_user f3_date
==================================================================
id1 u1 d1 u2 d2 u3 d3
id2 NULL NULL u4 d4 u5 d5

SQL code

SQL code

declare @T table (ID varchar(3),[User] varchar(2),Date varchar(2),Flow varchar(2))insert into @Tselect 'id1','u1','d1','f1' union allselect 'id1','u2','d2','f2' union allselect 'id1','u3','d3','f3' union allselect 'id2','u4','d4','f2' union allselect 'id2','u5','d5','f3'select id,    f1_user=max(case when flow='f1' then [user] else '' end),    f1_date=max(case when flow='f1' then date else '' end),    f2_user=max(case when flow='f2' then [user] else '' end),    f2_date=max(case when flow='f2' then date else '' end),    f3_user=max(case when flow='f3' then [user] else '' end),    f3_date=max(case when flow='f3' then date else '' end)from @t group by id/*id   f1_user f1_date f2_user f2_date f3_user f3_date---- ------- ------- ------- ------- ------- -------id1  u1      d1      u2      d2      u3      d3id2                  u4      d4      u5      d5*/
[解决办法]
SQL code
create table tb(ID varchar(10),[User] varchar(10),Date varchar(10),Flow varchar(10))insert into tbselect 'id1' ,'u1' ,'d1' ,'f1' union allselect 'id1' ,'u2' ,'d2' ,'f2' union allselect 'id1' ,'u3' ,'d3' ,'f3' union allselect 'id2' ,'u4' ,'d4' ,'f2' union allselect 'id2' ,'u5' ,'d5' ,'f3'godeclare @sql varchar(8000)set @sql = 'select ID'select @sql = @sql + ',max(case Flow when '''+Flow+''' then [User] else '''' end) as ['+Flow+'_User]'                   + ',max(case Flow when '''+Flow+''' then Date else '''' end) as ['+Flow+'_Date]'from (select distinct Flow from tb)tselect @sql = @sql + ' from tb group by ID'exec(@sql)drop table tb/**********ID         f1_User    f1_Date    f2_User    f2_Date    f3_User    f3_Date---------- ---------- ---------- ---------- ---------- ---------- ----------id1        u1         d1         u2         d2         u3         d3id2                              u4         d4         u5         d5(2 行受影响)