Transpose rows into columns using xslt with associated values

Question

I want to use xslt to transpose my input to generate a header for my csv file while printing associated values in the rows

Here is my sample XMl

<?xml version="1.0" encoding="UTF-8"?>
<rd:Root xmlns:rd="urn:com.report/G2N_Report">
    <rd:Row>
        <rd:ID>2134</rd:ID>
        <rd:Name>Abbi</rd:Name>
        <rd:Components>
            <rd:Component rd:Descriptor="FIU">2454.12</rd:Component>
            <rd:Component rd:Descriptor="Paid TO">504.00</rd:Component>
            <rd:Component rd:Descriptor="Imputed Income">0.57</rd:Component>
            <rd:Component rd:Descriptor="WHR">1778.71</rd:Component>
            <rd:Component rd:Descriptor="THR">2282.71</rd:Component>
            <rd:Component rd:Descriptor="Regular">1778.71</rd:Component>
            <rd:Component rd:Descriptor="Medical">-69.37</rd:Component>
        </rd:Components>
    </rd:Row>
    <rd:Row>
        <rd:ID>2020</rd:ID>
        <rd:Name>Abba</rd:Name>
        <rd:Components>
            <rd:Component rd:Descriptor="Imputed Income">0.21</rd:Component>
            <rd:Component rd:Descriptor="WHR">2080.50</rd:Component>
            <rd:Component rd:Descriptor="Addnl">228.00</rd:Component>
            <rd:Component rd:Descriptor="QBx2.5">641.25</rd:Component>
            <rd:Component rd:Descriptor="THR">2308.50</rd:Component>
            <rd:Component rd:Descriptor="Sick">228.00</rd:Component>
            <rd:Component rd:Descriptor="Regular">2000.71</rd:Component>
        </rd:Components>
    </rd:Row>
</rd:Root>

My xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:rd="urn:com.report/G2N_Report" exclude-result-prefixes="rd">
    
    <xsl:output method="text" encoding="UTF-8"/>
    <xsl:key name="descriptors" match="rd:Component" use="@rd:Descriptor"/>
    
    <!-- Template to match the root element -->
    <xsl:template match="/rd:Root">
        <!-- Output the CSV header -->
        <xsl:text>Employee ID,Name</xsl:text>
        <xsl:for-each
            select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
            <xsl:text>,</xsl:text>
            <xsl:value-of select="normalize-space(@rd:Descriptor)"/>
        </xsl:for-each>
        <xsl:text>&#10;</xsl:text>
        
        <!-- Process each Row -->
        <xsl:for-each select="rd:Row">
            
            <xsl:value-of select="rd:ID"/>
            <xsl:text>,</xsl:text>
            <xsl:value-of select="rd:Name"/>
            
            
            <!-- Process Components -->
            <xsl:for-each select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
                <xsl:text>,</xsl:text>
                <xsl:value-of select="../rd:Component[@rd:Descriptor = current()/@rd:Descriptor]"/>
            </xsl:for-each>
            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Desired Output

Employee ID,Name,FIU,Paid TO,Imputed Income,WHR,THR,Regular,Medical,Addnl,QBx2.5,Sick
2134,Abbi,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,0,0,0
2020,Abba,0,0,0.21,2080.50,2308.50,2000.71,0,228.00,641.25,228.00

Actual Output

Employee ID,Name,FIU,Paid TO,Imputed Income,WHR,THR,Regular,Medical,Addnl,QBx2.5,Sick
2134,Abbi,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,228.00,641.25,228.00
2020,Abba,2454.12,504.00,0.57,1778.71,2282.71,1778.71,-69.37,228.00,641.25,228.00

It seems my xslt is consolidating the values and repeating the same with every row. I need the associated values to be printed for each of the component columns and if component doesn't exist within a row then simply 0 should be printed. Please advise.

Mads Hansen · Accepted Answer · 2025-05-30 16:58:36Z

In your XSLT, when iterating over the rows you have another xsl:for-each to iterate over the columns and so relative XPath expressions will be from the context node of the selected rd:Component element. You need to anchor them to the rd:Row that is being iterated over in the outer for-each. The easiest way to do that is to let a variable and use it in the inner for-each. Additionally, if there is no value and you want to output 0 then you can test if number() returns a value or NaN and substitute a default value of 0:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:rd="urn:com.report/G2N_Report" exclude-result-prefixes="rd">

<xsl:output method="text" encoding="UTF-8"/>
<xsl:key name="descriptors" match="rd:Component" use="@rd:Descriptor"/>

<!-- Template to match the root element -->
<xsl:template match="/rd:Root">
    <!-- Output the CSV header -->
    <xsl:text>Employee ID,Name</xsl:text>
    <xsl:for-each
        select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
        <xsl:text>,</xsl:text>
        <xsl:value-of select="normalize-space(@rd:Descriptor)"/>
    </xsl:for-each>
    <xsl:text>&#10;</xsl:text>
    
    <!-- Process each Row -->
    <xsl:for-each select="rd:Row">
        
        <xsl:value-of select="rd:ID"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="rd:Name"/>
        
        <xsl:variable name="row" select="."/>
        <!-- Process Components -->
        <xsl:for-each select="//rd:Component[generate-id() = generate-id(key('descriptors', @rd:Descriptor)[1])]">
            <xsl:text>,</xsl:text>
            <xsl:variable name="value" select="$row/rd:Components/rd:Component[@rd:Descriptor = current()/@rd:Descriptor]"/>
            <xsl:choose>
                <xsl:when test="number($value)">
                    <xsl:value-of select="$value"/>
                </xsl:when>
                <xsl:otherwise>0</xsl:otherwise>
            </xsl:choose>
        </xsl:for-each>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>
</xsl:stylesheet>`

michael.hor257k · Accepted Answer · 2025-05-30 17:48:02Z

I would suggest a somewhat different approach, which I believe is both simpler and more efficient:

XSLT 2.0

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rd="urn:com.report/G2N_Report">
<xsl:output method="text" encoding="utf-8"/>

<xsl:key name="cmpnt" match="rd:Component" use="@rd:Descriptor" />

<xsl:template match="/rd:Root">
    <xsl:variable name="columns" select="distinct-values(rd:Row/rd:Components/rd:Component/@rd:Descriptor)"/>
    <!-- header -->
    <xsl:text>Employee ID,Name,</xsl:text>
    <xsl:value-of select="$columns" separator=","/> 
    <xsl:text>&#10;</xsl:text>
    <!-- data -->
    <xsl:for-each select="rd:Row">
        <!-- ID, Name -->
        <xsl:value-of select="rd:ID"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="rd:Name"/>
        <!-- components -->
        <xsl:variable name="curr-row" select="."/>
        <xsl:for-each select="$columns"> 
            <xsl:variable name="value" select="key('cmpnt', ., $curr-row)"/>
            <xsl:text>,</xsl:text>
            <xsl:value-of select="if($value) then $value else 0" />
        </xsl:for-each>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
 </xsl:template>

</xsl:stylesheet>

Collectives™ on Stack Overflow

Transpose rows into columns using xslt with associated values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related