Ordering the columns in a pivot table

[John Hall]

I have a data table with these 3 columns
SELECT [TestID],[ParameterID],[TextValue]
FROM [ParameterEntries]

select TestID, [1] as Param1, [2] as Param2, [3] as Param3, [4] as Param4, [5] as Param5
from (select TestID, TextValue,
row_number() over(partition by TestID order by ParameterID) rnk
from ParameterEntries WHERE TestID = (SELECT TOP 1 TestID FROM Subgroups WHERE CharID = 502107)
) p
pivot(max(TextValue) for rnk in ([1], [2], [3], [4], [5])) piv

TestID Param1 Param2 Param3 Param4 Param5
502014 20147338 3w 2.14 1.49 1.73

SELECT [PartID], [ParameterID], [PresentOrder]
FROM [ParametersUsed]
WHERE PartID = (SELECT PartID FROM CharList WHERE CharID = 502107)
ORDER BY PresentOrder

TestID ParameterID PresentOrder
502014 154 1
502014 502009 2
502014 502022 3
502014 502023 4
502014 502024 5

How do I put the Param1, Param2, Param3... in PresentOrder?

[Erland Sommarskog]

SELECT PE.TestID,
MIN(CASE PU.PresentOrder WHEN 1 THEN PE.TextValue END) AS Param1,
MIN(CASE PU.PresentOrder WHEN 2 THEN PE.TextValue END) AS Param2,
MIN(CASE PU.PresentOrder WHEN 3 THEN PE.TextValue END) AS Param3,
MIN(CASE PU.PresentOrder WHEN 4 THEN PE.TextValue END) AS Param4,
MIN(CASE PU.PresentOrder WHEN 5 THEN PE.TextValue END) AS Param5
FROM ParametersEntries PE
JOIN ParametersUser PU ON PE.TestID = PU.TestID
GROUP BY PE.TestID

You may note that I don't use the PIVOT keyward. The above method with CASE
is a lot more flexible and easier to understand.