au_id au_lname au_fname
------------------ ---------------- -----------
172-32-1176 White Johnson
172-32-1176 Green Marjorie
172-32-1176 Black hnson
172-11-1111 White bblam
想要的结果是
au_id White Green Black
------------------ ---------------- ----------- -----
172-32-1176 Johnson Marjorie hnson
172-11-1111 bblam null null
------------------ ---------------- -----------
172-32-1176 White Johnson
172-32-1176 Green Marjorie
172-32-1176 Black hnson
172-11-1111 White bblam
想要的结果是
au_id White Green Black
------------------ ---------------- ----------- -----
172-32-1176 Johnson Marjorie hnson
172-11-1111 bblam null null
insert T select '172-32-1176','White','Johnson'
union all select '172-32-1176','Green','Marjorie'
union all select '172-32-1176','Black','hnson'
union all select '172-11-1111','White','bblam'
declare @sql varchar(4000)
set @sql='select au_id,'
select @sql=@sql+quotename(au_lname)+'=max(case when au_lname='''+au_lname+''' then au_fname end),' from T
group by au_lname
select @sql=left(@sql, len(@sql)-1) +' from T group by au_id '
exec(@sql)
insert T select '172-32-1176','White','Johnson'
union all select '172-32-1176','Green','Marjorie'
union all select '172-32-1176','Black','hnson'
union all select '172-11-1111','White','bblam'declare @sql varchar(4000)
set @sql='select au_id,'
select @sql=@sql+quotename(au_lname)+'=max(case when au_lname='''+au_lname+''' then au_fname end),' from T
group by au_lname order by au_lname desc
select @sql=left(@sql, len(@sql)-1) +' from T group by au_id order by au_id desc'exec(@sql)