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

2.用pivot()动态行转列，但是时在SQL2005之后得版本才能用
想了解得请去我之前写的 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

2. 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