SqlServer 通过xml进行行转列

﹏ヽ暗。殇╰゛Y 2023-09-26 22:39 113阅读 0赞

一.Sqlserver行转列有三种方式  
1.对于确定得列字段可以用case when，这种用得少就不细说

2.用pivot()动态行转列，但是时在SQL2005之后得版本才能用  
想了解得请去我之前写的 [pivot动态行转列][pivot]

今天我想说的第三种方法是在SQLSERVER2005之前版本的行转列

1.  for xml path 就是将查询结果集以XML形式展现

select SPDM,gg1dm,
    (select ' ' +  gg2dm as gg2dm from FSENDMX a where a.djbh = 'ESFST230411103903' and a.spdm=b.spdm and a.gg1dm=b.gg1dm  for xml path('')) gg2
    ,(select ' ' + cast(cast(sl as int) as varchar(100)) sl from FSENDMX a where a.djbh = 'ESFST230411103903' and a.spdm=b.spdm and a.gg1dm=b.gg1dm  for xml path('')) sl  
    from FSENDMX b where DJBH ='ESFST230411103903' group by SPDM,gg1dm

![在这里插入图片描述][64272f4a795c43a293295b20ef1cb751.png]

1.  STUFF字符串函数是将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符，然后将第二个字符串插入到开始位置的第一个字符串中，语法如下：

STUFF（<character\_expression>，，，<character\_expression>）

然后将这两个一组合

WITH A AS(
    				  select  mx.spdm,spmc, gg1dm,g1.GGMC as gg1mc,sum(sl) sl,sum(je) as je,avg(dj) as dj from FSENDMX mx
    					inner JOIN SHANGPIN SP ON SP.SPDM=MX.SPDM
    					inner JOIN GUIGE1 G1 ON MX.GG1DM=G1.GGDM
    					where djbh='ESFST230411103903'
    					group by djbh, mx.spdm,spmc, gg1dm,g1.GGMC
    					
    			) 
    			SELECT spdm,spmc,gg1dm,gg1mc,sl,dj,je
    			,stuff(
    				 (select ' '+ gg2 from (select djbh, spdm, gg1dm,GGMC+'*'+cast(cast(sl as int) as varchar(100)) as gg2 
    				  from FSENDMX mx 
    				  inner JOIN GUIGE2 G2 ON MX.GG2DM=G2.GGDM) b 
    				  where DJBH ='ESFST230411103903' and SPDM=a.SPDM and GG1DM=a.GG1DM for xml path('')),1,1,''
    				 ) as cmmx
    from A a group by spdm,SPMC, gg1dm,gg1mc,sl,dj,je

![在这里插入图片描述][65ad0b6d53654798ab68110ffb7cff93.png]

[pivot]: https://blog.csdn.net/qq_38030465/article/details/109382434
[64272f4a795c43a293295b20ef1cb751.png]: https://img-blog.csdnimg.cn/64272f4a795c43a293295b20ef1cb751.png
[65ad0b6d53654798ab68110ffb7cff93.png]: https://img-blog.csdnimg.cn/65ad0b6d53654798ab68110ffb7cff93.png