----here's the query I use to figure out what the max number of elements in my future pivots are going to be.
--I run this twice. Once for all components of a goal's measurement constructor, once for its INPUTS ONLY [Dropdowns, textboxes]
--;WITH countGoalItems AS (
--SELECT dm2.MeasurementID, COUNT(DISTINCT dmi.MeasurementItemID) AS ItemCount
--FROM dbo.dtMeasurements AS dm2 -- dtMeasurements [not to be confused with dMeasurements] is where the template of a measure [or goal] lives
--JOIN dbo.dtMeasurementItem AS dmi -- dtMeasurementItems [not to be confused with dMeasurementItems] is where the components of a measure live.
--     ON dmi.MeasurementID = dm2.MeasurementID --joins on the [template] MeasurementID
--            AND dmi.GoalRank > 0 --if this is greater than 0, it's a goal item.
--            --AND dmi.Type <> 'Label' -- this is how you run for inputs only!!! uncomment this
--LEFT JOIN dbo.dtFieldPopulatorItems AS dfpi
--     ON  CONVERT(VARCHAR(36), dmi.Stmt) = CONVERT(VARCHAR(36), dfpi.FieldPopulatorID)

--WHERE dm2.Active = 1  --if you only care about active measures, uncomment this. If you want a full history, don't.

--GROUP BY dm2.MeasurementID
--)

----This is how I arrived at the highest number of items in an active goal currently being 14, including labels, 8 without labels.
--SELECT MAX(ItemCount) FROM countGoalItems

--now the real query.

IF OBJECT_ID('tempdb..#NormalizedGoalConstructors') IS NOT NULL DROP TABLE #NormalizedGoalConstructors

SELECT
       dm3.ModuleID
       , dm3.Modulename
       , dm2.MeasurementID
       , dm2.MeasurementName
       , CASE dmi.Type WHEN 'Label' THEN dmi.Stmt ELSE '#' END AS OutputStatement
       -- the field name of a component in the goal constructor, or "Text" if it's a box, or empty-string if it's a label.
       , FieldPopName = ISNULL(dfp.Field, IIF(dmi.Type = 'Text Box', 'Text', ''))
       -- here's where some magic occurs.
       , ROW_NUMBER() OVER(PARTITION BY dm3.ModuleID, dm2.MeasurementID ORDER BY dmi.GoalRank) AS ComponentIndex
INTO #NormalizedGoalConstructors
FROM dbo.dtMeasurements AS dm2
-- I slip this between the measure constructor and its contents because it confers a 1:1, it's optimized here as opposed to below, ie: does less work.
JOIN dbo.dtModules AS dm3 ON dm3.ModuleID = dm2.ModuleID --this is solely to get the name of the module.
JOIN dbo.dtMeasurementItem AS dmi ON dmi.MeasurementID = dm2.MeasurementID --joins constructor to its components
       AND dmi.GoalRank > 0 --if this is greater than 0, it's a goal item.
LEFT JOIN dbo.dtFieldPopulator AS dfp ON CONVERT(VARCHAR(36), dfp.FieldPopulatorID) = CONVERT(VARCHAR(36), dmi.Stmt) -- the name of the field pop lives here
WHERE dm2.Active = 1 --again, constrain active

IF OBJECT_ID('tempdb..#NormalizedGoalConstructorInputsOnly') IS NOT NULL DROP TABLE #NormalizedGoalConstructorInputsOnly

--now another.. but this one excludes labels and gets inputs only
SELECT
       dm3.ModuleID
       , dm3.Modulename
       , dm2.MeasurementID
       , dm2.MeasurementName
       -- the field name of a component in the goal constructor, or "Text" if it's a box, or empty-string if it's a label.
       , FieldPopName = ISNULL(dfp.Field, 'Text')
       -- here's where some magic occurs.
       , DENSE_RANK() OVER(PARTITION BY dm3.ModuleID, dm2.MeasurementID ORDER BY dmi.GoalRank) AS ComponentIndex
INTO #NormalizedGoalConstructorInputsOnly
FROM dbo.dtMeasurements AS dm2
-- I slip this between the measure constructor and its contents because it confers a 1:1, it's optimized here as opposed to below, ie: does less work.
JOIN dbo.dtModules AS dm3 ON dm3.ModuleID = dm2.ModuleID --this is solely to get the name of the module.
JOIN dbo.dtMeasurementItem AS dmi ON dmi.MeasurementID = dm2.MeasurementID --joins constructor to its components
       AND dmi.GoalRank > 0 --if this is greater than 0, it's a goal item.
       AND dmi.Type <> 'Label'
LEFT JOIN dbo.dtFieldPopulator AS dfp ON CONVERT(VARCHAR(36), dfp.FieldPopulatorID) = CONVERT(VARCHAR(36), dmi.Stmt) -- the name of the field pop lives here
WHERE dm2.Active = 1 --again, constrain active

IF OBJECT_ID('tempdb..#PivotedConstructor') IS NOT NULL DROP TABLE #PivotedConstructor

--now the fun part
SELECT
       Modulename
       , MeasurementID
       , MeasurementName
       --concats are rad, they do something really wicked. This is a T-SQL thing.
       , CONCAT(
              [1] + ' '
              , [2] + ' '
              , [3] + ' '
              , [4] + ' '
              , [5] + ' '
              , [6] + ' '
              , [7] + ' '
              , [8] + ' '
              , [9] + ' '
              , [10] + ' '
              , [11] + ' '
              , [12] + ' '
              , [13] + ' '
              , [14]
              ) AS Constructor
INTO #PivotedConstructor
FROM (
       SELECT
              g.ModuleName
              , g.MeasurementID
              , g.MeasurementName
              , g.ComponentIndex
              , g.OutputStatement
       FROM #NormalizedGoalConstructors AS g
       ) AS SourceTable --this name is just another alias, you can name it anything
PIVOT
(
       MAX(OutputStatement)
       FOR ComponentIndex IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14])
) AS PivotTable --again, just a name


IF OBJECT_ID('tempdb..#PivotedConstructorInputs') IS NOT NULL DROP TABLE #PivotedConstructorInputs

--now the fun part 2
SELECT
       Modulename
       , MeasurementID
       , MeasurementName
       , [1]
       , [2]
       , [3]
       , [4]
       , [5]
       , [6]
       , [7]
       , [8]
INTO #PivotedConstructorInputs
FROM (
       SELECT
              g.ModuleName
              , g.MeasurementID
              , g.MeasurementName
              , g.ComponentIndex
              , g.FieldPopName
       FROM #NormalizedGoalConstructorInputsOnly AS g
       ) AS SourceTable --this name is just another alias, you can name it anything
PIVOT
(
       MAX(FieldPopName)
       FOR ComponentIndex IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PivotTable --again, just a name

SELECT
       pci.Modulename
       , pci.MeasurementName
       , pc.Constructor
       , pci.[1]
       , pci.[2]
       , pci.[3]
       , pci.[4]
       , pci.[5]
       , pci.[6]
       , pci.[7]
       , pci.[8]
FROM #PivotedConstructor AS pc
JOIN #PivotedConstructorInputs AS pci
       ON pc.MeasurementID = pci.MeasurementID
ORDER BY pci.Modulename