SQL pivot

Question

How do I use the "pivot " to convert?

SELECT     

DOC_NBR,
types.col.value('(./@fieldId)[1]', 'nvarchar(MAX)') AS fieldId,

types.col.value('(./@fieldValue)[1]', 'nvarchar(MAX)') AS fieldValue

FROM dbo.TB_WKF_TASK CROSS APPLY CURRENT_DOC.nodes('//FieldItem/DataGrid/Row/Cell') AS types(col)
    
WHERE DOC_NBR LIKE 'BCF%';

Answer 1

Hi @Dylan,

Please try the following solution.

It is using SQL Server's XQuery to shred the XML.

-- DDL and sample data population, start
DECLARE @tbl TABLE (DOC_NBR VARCHAR(20) PRIMARY KEY,CURRENT_DOC XML);
INSERT @tbl (DOC_NBR, CURRENT_DOC) VALUES
('BCF230600135',  
    N'<Form formVersionId="207f742">
	<Applicant userGuid="3565-4acd" account="com1" name="南"/>
	<FormFieldValue>
		<FieldItem fieldId="VAA_Form" fieldValue="BCF230600038" realValue="" enableSearch="True"/>
		<FieldItem fieldId="VAA_DP" fieldValue="C02000" realValue="" enableSearch="True"/>
		<FieldItem fieldId="VAA_Applicant" fieldValue="201079" realValue=";" enableSearch="True"/>
		<FieldItem fieldId="VAA_Visitor" enableSearch="True" fillerName="" fillerUserGuid="c2a8591c33" fillerAccount="201079" fillSiteId="">
			<DataGrid>
				<Row order="0">
					<Cell fieldId="VAA_CP" fieldValue="Energy" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_Name" fieldValue="Sean" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_Vtittle" fieldValue="Engineering Director" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_Tel" fieldValue="5098" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_CNumber" fieldValue="無" realValue="" customValue="" enableSearch="True"/>
				</Row>
			</DataGrid>
		</FieldItem>
		<FieldItem fieldId="VAA_Check" fieldValue="用餐" realValue="" customValue="@null" enableSearch="True" fillerName="柏" fillerUserGuid="c2a8591c33" fillerAccount="201079" fillSiteId=""/>
		<FieldItem fieldId="VAA_Meals" enableSearch="True" fillerName="柏" fillerUserGuid="c2a8591c33" fillerAccount="201079" fillSiteId="">
			<DataGrid>
				<Row order="0">
					<Cell fieldId="VAA_MDate" fieldValue="2023/06/14" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_Mcheck" fieldValue="130元" realValue="" customValue="" enableSearch="True" fieldMessage=""/>
					<Cell fieldId="VAA_Text" fieldValue="7" realValue="" customValue="" enableSearch="True"/>
				</Row>
				<Row order="1">
					<Cell fieldId="VAA_MDate" fieldValue="2023/06/14" realValue="" customValue="" enableSearch="True"/>
					<Cell fieldId="VAA_Mcheck" fieldValue="130元" realValue="" customValue="" enableSearch="True" fieldMessage=""/>
					<Cell fieldId="VAA_Text" fieldValue="1" realValue="" customValue="" enableSearch="True"/>
				</Row>
			</DataGrid>
		</FieldItem>
	</FormFieldValue>
</Form>');
-- DDL and sample data population, end

SELECT DOC_NBR
	, c.value('(Cell[@fieldId="VAA_CP"]/@fieldValue)[1]', 'NVARCHAR(30)') AS VAA_CP
	, c.value('(Cell[@fieldId="VAA_Name"]/@fieldValue)[1]', 'NVARCHAR(30)') AS VAA_Name
	, c.value('(Cell[@fieldId="VAA_Vtittle"]/@fieldValue)[1]', 'NVARCHAR(30)') AS VAA_Vtittle
	, c.value('(Cell[@fieldId="VAA_Tel"]/@fieldValue)[1]', 'NVARCHAR(30)') AS VAA_Tel
	, c.value('(Cell[@fieldId="VAA_CNumber"]/@fieldValue)[1]', 'NVARCHAR(30)') AS VAA_CNumber
FROM @tbl
	CROSS APPLY current_doc.nodes('/Form/FormFieldValue/FieldItem[@fieldId="VAA_Visitor"]/DataGrid/Row') AS t(c);

Answer 2

Hi @Dylan

You can try this query. Since there is a string in your example table that I can't type, I used 'zhou' instead.

create table test(DOC_NBR varchar(50),fieldid varchar(50),fieldValue varchar(50));
insert into test values
('BCF230500181','VAA_CP','zhou'),
('BCF230500181','VAA_Name',''),
('BCF230500181','VAA_Vtittle',''),
('BCF230500181','VAA_Tel',''),
('BCF230500181','VAA_CNumber','');

select * from test
pivot(max(fieldValue) for fieldid in(VAA_CP,VAA_Name,VAA_Vtittle,VAA_Tel,VAA_CNumber)) as t;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Are the XML attribute fix? Then you don't need pivot, this XQuery should give you the wanted result:

DECLARE @xml xml =
N'<FormFieldValue>
<FieldItem fieldId="VAA_Visitor" enableSearch="True" fillerName="李慧" fillerUserGuid="47f3-b276" fillerAccount="20239999" fillSiteId="">
      <DataGrid>
        <Row order="0">
          <Cell fieldId="VAA_CP" fieldValue="經濟部" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Name" fieldValue="玉雯" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Vtittle" fieldValue="經理" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Tel" fieldValue="03-2345631" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_CNumber" fieldValue="" realValue="" customValue="" enableSearch="True" />
        </Row>
        <Row order="1">
          <Cell fieldId="VAA_CP" fieldValue="工業局" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Name" fieldValue="嘉U" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Vtittle" fieldValue="協理" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_Tel" fieldValue="09-23442031" realValue="" customValue="" enableSearch="True" />
          <Cell fieldId="VAA_CNumber" fieldValue="" realValue="" customValue="" enableSearch="True" />
        </Row>
      </DataGrid>
    </FieldItem>
</FormFieldValue>';

SELECT prods.value('@order[1]', 'nvarchar(10)') as orderNo,
       prods.value('(Cell/@fieldValue)[1]', 'nvarchar(10)') as VAA_CP,
	   prods.value('(Cell/@fieldValue)[2]', 'nvarchar(10)') as VAA_Name,
	   prods.value('(Cell/@fieldValue)[3]', 'nvarchar(10)') as VAA_Vtittle,
	   prods.value('(Cell/@fieldValue)[4]', 'nvarchar(10)') as VAA_Tel,
	   prods.value('(Cell/@fieldValue)[5]', 'nvarchar(10)') as VAA_CNumber
FROM @xml.nodes(N'(//FormFieldValue/FieldItem/DataGrid/Row)') AS prod(prods)