Unpivoting Tables with Table UnPivot Transforms

1: Summary

The table unPivot transform creates a new table where each row represents a variable from a list or dimensionGroup. This is useful when you want to calculate scores or assign labels per variable—perfect for visualization, exports, or simplified analysis.

2: Declaring the Table

table unPivot #yourTableName {
parent: .yourParentTable:
dimensionGroup: @yourDimensionGroup
action create {
// Variables created per pivoted variable
}
}

2.1: Key Properties

Property	Description
parent	The parent table this unPivoted table will be nested inside (e.g., .responses:).
dimensionGroup	(Required OR use variables) Reference to a dimensionGroup for sourcing variables.
variables	(Optional) Use a specific list of vPaths instead of a dimensionGroup.
filter expression	Optional conditions to include/exclude rows.

Note: Only one of dimensionGroup or variables can be used.

3: Creating Variables Per Row

Each row in the new table will represent one variable from the dimensionGroup or list. You can use the variableValue() function to access the value of that variable.

action create {
  variable numeric #questionScoreValue {
    totalDigits: 1
    decimals: 0
    value: variableValue()
    required: true
  }
}

4: Example: Recoded Distributions and Auto Variables

Use auto variables to pull metadata or raw responses alongside scores.

action create {
  variable singleChoice #threePoint {
    value: recode(questionScore:questionScoreValue, @question3point)
    label: "Distribution"
    required: true
    option code { code: "3", label: "Unfavorable" }
    option code { code: "2", label: "Neutral" }
    option code { code: "1", label: "Favorable" }
  }
 
       variable auto #PH10039 {
    value: .responses:PH10039
  }

4.1: Variable Types Allowed

numeric, singleChoice, auto, boolean, etc.

5: Full Example

table unPivot #dg1_questionScore {
  parent: .responses:
  dimensionGroup: @dg1
  
    action create {
    variable numeric #questionScoreValue {
    totalDigits: 1
    decimals: 0
    value: variableValue()
    required: true
    }
  
      variable singleChoice #threePoint {
      value: recode(questionScore:questionScoreValue, @question3point)
      label: "Distribution"
      required: true
      option code { code: "3", label: "Unfavorable" }
      option code { code: "2", label: "Neutral" }
      option code { code: "1", label: "Favorable" }
    }
  
        variable auto #PH10039 {
      value: .responses:PH10039
    }
  }
}

6: Best Practices

• Use dimensionGroup for structured and scalable input variables.

• Combine with recode() to create simplified views (e.g., favorable/unfavorable).

• Use auto variables to mirror structure or schema from original data.

• If using filters, be sure all filter expression entities evaluate to true for row creation.