How do I order the columns on a pivot table?

[John Hall]

I have a data table with these 3 columns
SELECT [TestID],[ParameterID],[TextValue] FROM [ParameterEntries]
And a setup table with these 3 columns
SELECT [PartID], [ParameterID], [PresentOrder] FROM [ParametersUsed]

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
506789 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

PartID 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

[John Hall]

Post by 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

ParametersUsed does not have a TestID column. But, I think I need to create a recordset from a join of ParametersUsed and ParameterEntries PU.ParameterId = PE.ParameterID

[Erland Sommarskog]

Post by John Hall
ParametersUsed does not have a TestID column. But, I think I need to
create a recordset from a join of ParametersUsed and ParameterEntries
PU.ParameterId = PE.ParameterID

I got the impression that it had a TestID from your one of your posts.

Post by John Hall
So, why is Param1 getting duplicated into Param2 but 3, 4, & 5 are fine?

For this type of problem, it often helps if you post CREATE TABLE statements
for your tables, INSERT statements with sample data, enough to illustrate
all angles of the problem. This makes it simple to copy and paste into a
query window to develop a tested solution. Don't forget to say which version
SQL Server you are using.

[John Hall]

Post by John Hall

Post by 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

ParametersUsed does not have a TestID column. But, I think I need to create a recordset from a join of ParametersUsed and ParameterEntries PU.ParameterId = PE.ParameterID

It was strange. I replaced the TestID with ParameterID, but the 1 PE.TextValue duplicated into the 2nd column.??? but, the 3rd, 4th and 5th columns were correct. I changed from MIN to MAX and fixed that problem.

RESOLVED!!!

[Erland Sommarskog]

Post by John Hall
It was strange. I replaced the TestID with ParameterID, but the 1
PE.TextValue duplicated into the 2nd column.??? but, the 3rd, 4th and
5th columns were correct. I changed from MIN to MAX and fixed that
problem.

MIN or MAX should not have any importance if it is a proper pivot. But
since you never gave me the exact table defintions, all I could offer was
guesses anyway.

[John Hall]

Post by 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

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 ParameterEntries PE
JOIN ParametersUsed PU ON PE.ParameterID = PU.ParameterID
WHERE TestID IN (SELECT TestID FROM Subgroups WHERE CharID = 502107 AND DateTime = '08/29/2017 10:29:25' )
GROUP BY PE.TestID
TestID Param1 Param2 Param3 Param4 Param5
519802 201487332 201487332 2.20 1.54 1.80

SELECT ParameterID, TextValue FROM ParameterEntries WHERE TestID = 519802
154 201487332
502009 5w
502022 2.20
502023 1.54
502024 1.80

So, why is Param1 getting duplicated into Param2 but 3, 4, & 5 are fine?

[John Hall]

Post by 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

The SQL Management studio is saying this is a view not a pivot table and can't have an ORDER BY. I simplified this for my question. I need to order the results and JOIN it on TestID with yet another table.

[Erland Sommarskog]

Post by John Hall

Post by 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

The SQL Management studio is saying this is a view not a pivot table and
can't have an ORDER BY. I simplified this for my question. I need to
order the results and JOIN it on TestID with yet another table.

Could explain more closely what you are doing? The above is not a view, it
is a query, since, well, that seemed to be what you asked for. But
obviously, you could make a it view by adding CREATE VIEW on top.

You cannot have ORDER BY in a view definition.

[John Hall]

Post by Erland Sommarskog

Post by John Hall

Post by 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

The SQL Management studio is saying this is a view not a pivot table and
can't have an ORDER BY. I simplified this for my question. I need to
order the results and JOIN it on TestID with yet another table.

Could explain more closely what you are doing? The above is not a view, it
is a query, since, well, that seemed to be what you asked for. But
obviously, you could make a it view by adding CREATE VIEW on top.
You cannot have ORDER BY in a view definition.

SELECT DateTime, Param1, Param2, Param3, Param4, Param5, S1, S2, S3, S4, S5
FROM ( SELECT TOP 10000 SubgroupID, DateTime, TestID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC ) s
LEFT JOIN (
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 IN (SELECT TOP 10000 TestID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC)
) p
pivot(max(TextValue) for rnk in ([1], [2], [3], [4], [5])) piv) pv
ON s.TestID = pv.TestID
RIGHT JOIN
(select SubgroupID, [1] as S1, [2] as S2, [3] as S3, [4] as S4, [5] as S5
from (select SubgroupID, Value,
row_number() over(partition by SubgroupID order by SampleNumber) rnk
from DataValues WHERE SubgroupID IN (SELECT TOP 10000 SubgroupID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC)
) d
pivot(max(Value) for rnk in ([1], [2], [3], [4], [5])) piv) pv2
ON s.SubgroupID = pv2.SubgroupID
ORDER BY DateTime

I want to replace the ParameterEntries query with one that orders the TextValues in the order of their ParameterIDs in PresentOrder in ParametersUsed

[John Hall]

Post by John Hall

Post by Erland Sommarskog

Post by John Hall

Post by 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

The SQL Management studio is saying this is a view not a pivot table and
can't have an ORDER BY. I simplified this for my question. I need to
order the results and JOIN it on TestID with yet another table.

Could explain more closely what you are doing? The above is not a view, it
is a query, since, well, that seemed to be what you asked for. But
obviously, you could make a it view by adding CREATE VIEW on top.
You cannot have ORDER BY in a view definition.

SELECT DateTime, Param1, Param2, Param3, Param4, Param5, S1, S2, S3, S4, S5
FROM ( SELECT TOP 10000 SubgroupID, DateTime, TestID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC ) s
LEFT JOIN (
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 IN (SELECT TOP 10000 TestID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC)
) p
pivot(max(TextValue) for rnk in ([1], [2], [3], [4], [5])) piv) pv
ON s.TestID = pv.TestID
RIGHT JOIN
(select SubgroupID, [1] as S1, [2] as S2, [3] as S3, [4] as S4, [5] as S5
from (select SubgroupID, Value,
row_number() over(partition by SubgroupID order by SampleNumber) rnk
from DataValues WHERE SubgroupID IN (SELECT TOP 10000 SubgroupID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC)
) d
pivot(max(Value) for rnk in ([1], [2], [3], [4], [5])) piv) pv2
ON s.SubgroupID = pv2.SubgroupID
ORDER BY DateTime
I want to replace the ParameterEntries query with one that orders the TextValues in the order of their ParameterIDs in PresentOrder in ParametersUsed

I want to return all, but if I take out the TOP # it errors. So, I did a COUNT() query before and inserted the COUNT() value in the TOP #

[Erland Sommarskog]

Post by John Hall
I want to replace the ParameterEntries query with one that orders the
TextValues in the order of their ParameterIDs in PresentOrder in
ParametersUsed

Please post CREATE TABLE statements for your tables and INSERT statements
with sample data, enough to illustrate all angles of the problem, and the
expected results given the query. I can't work from your query. It's using
the PIVOT keyword (which I never bothered to learn) and the RIGHT JOIN
operator (which gives me a headache).

[John Hall]

Post by Erland Sommarskog

Post by John Hall

Post by 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

The SQL Management studio is saying this is a view not a pivot table and
can't have an ORDER BY. I simplified this for my question. I need to
order the results and JOIN it on TestID with yet another table.

Could explain more closely what you are doing? The above is not a view, it
is a query, since, well, that seemed to be what you asked for. But
obviously, you could make a it view by adding CREATE VIEW on top.
You cannot have ORDER BY in a view definition.

SELECT DateTime, Param1, Param2, Param3, Param4, Param5, S1, S2, S3, S4, S5
FROM ( SELECT TOP 100 SubgroupID, DateTime, TestID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC ) s
LEFT JOIN (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 ParameterEntries PE
JOIN ParametersUsed PU ON PE.ParameterID = PU.ParameterID
WHERE TestID IN (SELECT TOP 100 TestID FROM Subgroups WHERE CharID = 502107 AND DateTime = '08/29/2017 10:29:25' )
GROUP BY PE.TestID) pv
ON s.TestID = pv.TestID
RIGHT JOIN
(select SubgroupID, [1] as S1, [2] as S2, [3] as S3, [4] as S4, [5] as S5
from (select SubgroupID, Value,
row_number() over(partition by SubgroupID order by SampleNumber) rnk
from DataValues WHERE SubgroupID IN (SELECT TOP 100 SubgroupID FROM Subgroups WHERE CharID = 502107 AND DateTime < '04/22/2020' ORDER BY SubgroupID DESC)
) d
pivot(max(Value) for rnk in ([1], [2], [3], [4], [5])) piv) pv2
ON s.SubgroupID = pv2.SubgroupID
ORDER BY DateTime

This time it didn't give an error but, returned NULL for all ParamX values and incorrect DataValues

[Erland Sommarskog]

Post by John Hall
This time it didn't give an error but, returned NULL for all ParamX values
and incorrect DataValues

Please refer to my previous post. I don't know your data and table, and I
am not inclined to make further gueses.