SQLSERVER-XML-列转行

数据源：

select * from table1;

其中DATAVALUES的值为：

<formData tag="1"> <result type="data" tag="2"> <row> <field name="科室" type="" prop="" value="研发部"/> <field name="医生" type="" prop="" value="张飞"/> <field name="聘任时间" type="5" prop="null" value="2016-09-01" necessary="1"/> <field name="职称" type="1" prop="null" value="" necessary="0"/> <field name="手术等级" type="4" prop="22559" value="一级" necessary="0"/> <field name="手术1" type="1" prop="null" value="" necessary="0"/> <field name="姓名11" type="1" prop="null" value="" necessary="0"/> <field name="病案11" type="1" prop="null" value="" necessary="0"/> <field name="姓名12" type="1" prop="null" value="" necessary="0"/>< field name="病案12" type="1" prop="null" value="" necessary="0"/> <field name="姓名13" type="1" prop="null" value="" necessary="0"/> <field name="病案13" type="1" prop="null" value="" necessary="0"/> <field name="姓名14" type="1" prop="null" value="" necessary="0"/> <field name="病案14" type="1" prop="null" value="" necessary="0"/> <field name="姓名15" type="1" prop="null" value="" necessary="0"/> <field name="病案15" type="1" prop="null" value="" necessary="0"/> <field name="手术2" type="1" prop="null" value="" necessary="0"/> <field name="姓名21" type="1" prop="null" value="" necessary="0"/> <field name="病案21" type="1" prop="null" value="" necessary="0"/> <field name="姓名22" type="1" prop="null" value="" necessary="0"/> <field name="病案22" type="1" prop="null" value="" necessary="0"/> <field name="姓名23" type="1" prop="null" value="" necessary="0"/> <field name="病案23" type="1" prop="null" value="" necessary="0"/> <field name="姓名24" type="1" prop="null" value="" necessary="0"/> <field name="病案24" type="1" prop="null" value="" necessary="0"/> <field name="姓名25" type="1" prop="null" value="" necessary="0"/> <field name="病案25" type="1" prop="null" value="" necessary="0"/> <field name="手术3" type="1" prop="null" value="" necessary="0"/> <field name="姓名31" type="1" prop="null" value="" necessary="0"/> <field name="病案31" type="1" prop="null" value="" necessary="0"/> <field name="姓名32" type="1" prop="null" value="" necessary="0"/> <field name="病案32" type="1" prop="null" value="" necessary="0"/> <field name="姓名33" type="1" prop="null" value="" necessary="0"/> <field name="病案33" type="1" prop="null" value="" necessary="0"/> <field name="姓名34" type="1" prop="null" value="" necessary="0"/> <field name="病案34" type="1" prop="null" value="" necessary="0"/> <field name="姓名35" type="1" prop="null" value="" necessary="0"/> <field name="病案35" type="1" prop="null" value="" necessary="0"/> <field name="手术4" type="1" prop="null" value="" necessary="0"/> <field name="姓名41" type="1" prop="null" value="" necessary="0"/> <field name="病案41" type="1" prop="null" value="" necessary="0"/> <field name="姓名42" type="1" prop="null" value="" necessary="0"/> <field name="病案42" type="1" prop="null" value="" necessary="0"/> <field name="姓名43" type="1" prop="null" value="" necessary="0"/> <field name="病案43" type="1" prop="null" value="" necessary="0"/> <field name="姓名44" type="1" prop="null" value="" necessary="0"/> <field name="病案44" type="1" prop="null" value="" necessary="0"/> <field name="姓名45" type="1" prop="null" value="" necessary="0"/> <field name="病案45" type="1" prop="null" value="" necessary="0"/> <field name="手术5" type="1" prop="null" value="" necessary="0"/> <field name="姓名51" type="1" prop="null" value="" necessary="0"/> <field name="病案51" type="1" prop="null" value="" necessary="0"/> <field name="姓名52" type="1" prop="null" value="" necessary="0"/> <field name="病案52" type="1" prop="null" value="" necessary="0"/> <field name="姓名53" type="1" prop="null" value="" necessary="0"/> <field name="病案53" type="1" prop="null" value="" necessary="0"/> <field name="姓名54" type="1" prop="null" value="" necessary="0"/> <field name="病案54" type="1" prop="null" value="" necessary="0"/> <field name="姓名55" type="1" prop="null" value="" necessary="0"/> <field name="病案55" type="1" prop="null" value="" necessary="0"/> <field name="附件" type="12" prop="null" value="" necessary="0"/> <field name="备注" type="2" prop="null" value="" necessary="0"/> </row></result></formData>

目的：将字段DATAVALUES值的field标签的name属性作为字段名，value属性作为字段值。设置为与DATAVALUES、CREATEDATE同级别的字段；

方案：

CREATE PROC P1( @TEMPLATEID varchar(10), @COLUMNS VARCHAR(MAX) ) AS DECLARE @SQL VARCHAR(2000) SET @SQL=' SELECT * FROM (   Select ID,TEMPLATEID,Convert(Varchar(20),CREATEDATE,20)CREATEDATE,T.ROWS.value(''@name'',''varchar(MAX)'') NAME,   T.ROWS.value(''@value'',''varchar(MAX)'') VALUE   from table1   CROSS APPLY DATAVALUES.nodes(''/formData/result/row/field'')                 AS T(ROWS)    where TEMPLATEID ='+@TEMPLATEID+' ) A PIVOT (MAX(A.VALUE) FOR A.NAME IN ('+@COLUMNS+') ) AS P' --PRINT @SQL EXEC (@SQL)

执行：

EXEC BASIC_GET_FORMPAGE_DATALIST '24','科室,日期,医师,职称,聘任时间,手术名称,患者姓名1,患者姓名2,患者姓名3,患者姓名4,患者姓名5,病案号1,病案号2,病案号3,病案号4,病案号5,操作时间1,操作时间2,操作时间3,操作时间4,操作时间5,医师签名1,医师签名2,医师签名3,医师签名4,医师签名5,签名时间1,签名时间2,签名时间3,签名时间4,签名时间5,备注'

结果：