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]

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

[John Hall]

On Sunday, April 12, 2020 at 7:59:26 AM UTC-4, Erland Sommarskog wrote:

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?

[Erland Sommarskog]

John Hall (founda...@gmail.com) writes:
> 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.

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

On Sunday, April 12, 2020 at 7:59:26 AM UTC-4, Erland Sommarskog wrote:

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]

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]

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]

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]

John Hall (founda...@gmail.com) writes:
> 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]

On Sunday, April 12, 2020 at 4:31:07 PM UTC-4, Erland Sommarskog wrote:

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]

John Hall (founda...@gmail.com) writes:
> 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.

[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.

RESOLVED!!!

[Erland Sommarskog]

John Hall (founda...@gmail.com) writes:
> 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.