mssql动态行列转置的问题？

如题，查询语句：select id,zy_id,lx,icd from h_zyzd where lx<>0 and zy_id='200002466' order by id
--按住院号询所有诊断
id |zy_id |lx|icd |
-----|---------|--|-------|
10308|200002466|1 |S42.300|
10310|200002466|3 |S44.200|
10311|200002466|3 |S22.400|
10312|200002466|3 |S27.000|
10313|200002466|3 |D64.902|
10314|200002466|3 |E11.900|
10315|200002466|3 |I10.x09|
10316|200002466|3 |S00.801|
10317|200002466|3 |G51.003|
因为每个住院号lx=1的icd是唯一的，lx=3的结果个数是，想将以上查询结果按id顺序行列转置为以下格式，请问动态sql语句应该写？
zy_id |icd1 |icd2 |icd3 |icd4 |icd5
200002466 |S42.300 |S44.200 |S22.400 ||S27.000|D64.902|

可以用 SQL Server 的 PIVOT TABLE 功能。具体语法请参考　https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15　

将列数据当成列名并查询查询数据_Fjiaer的博客-CSDN博客 将列数据当成列名并查询查询数据一、需求二、模拟表三、要实现的效果四、思路注意事项一、需求sqlserver使用存储过程将列数据当成列名并查询查询数据二、模拟表项目Value电脑$1600手机$12导管$1idnamesizenumber1aS42aM83aL44aM75cS46bM87cS48dM89dS410eM8 https://blog.csdn.net/Fjiaer/article/details/121241090?spm=1001.2014.3001.5502

单纯用FOR XML PATH 无法解决两个动态列的转换，参考了https://blog.csdn.net/weixin_46348403/article/details/120527757、https://blog.csdn.net/tr1912/article/details/78888020、https://bbs.csdn.net/topics/330207060这些大神的解决思路，目前实现单个zy_id的行列转换，因为我要使用此查询结果联动更新其它表，所以还得研究怎么使用 row_number作为id0, 作为pivot函数 in里面的参数，目前在sql2008使用 pivot(max(icd) for id0(1,2,3,4...))b 总是报 1 格式错误,待研究

select zy_id ,[1] icd0,[2] icd1,[3]icd2,[4]icd3,[5]icd4,[6]icd5,[7]icd6,[8]icd7,[9]icd8,[10]icd9,[11]icd10 from
(select top 11 ROW_NUMBER () over (PARTITION BY zy_id ORDER BY lx) as id0,zy_id,icd
from h_zyzd where lx in(0,3) order by id) a
pivot (min(icd) for id0 in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]))b,

使用ROW_NUMBER，配合 PARTITION BY完美解决问题