X-Received: by 2002:a0c:8326:: with SMTP id j35mr14472072qva.205.1586725830402; Sun, 12 Apr 2020 14:10:30 -0700 (PDT)
X-Received: by 2002:ac8:36ab:: with SMTP id a40mr8582018qtc.309.1586725830150; Sun, 12 Apr 2020 14:10:30 -0700 (PDT)
Path: csiph.com!xmission!news.alt.net!feeder.usenetexpress.com!tr2.iad1.usenetexpress.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: microsoft.public.sqlserver.programming
Date: Sun, 12 Apr 2020 14:10:29 -0700 (PDT)
In-Reply-To: <XnsAB9DE50FF9274Yazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: google-groups.googlegroups.com; posting-host=173.162.45.17; posting-account=oUsksgoAAADjmotEgGTiI_3PeNLPQGJq
NNTP-Posting-Host: 173.162.45.17
References: <32b39068-acc8-48ef-ac56-7f80bc2ee65b@googlegroups.com> <XnsAB9D8E503D7CEYazorman@127.0.0.1> <7a25939e-1e2d-4de3-9315-edef91428a1a@googlegroups.com> <XnsAB9DE50FF9274Yazorman@127.0.0.1>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <bba1f50c-e0ae-4466-a0fb-fee3ef82c465@googlegroups.com>
Subject: Re: How do I order the columns on a pivot table?
From: John Hall <foundarecord@gmail.com>
Injection-Date: Sun, 12 Apr 2020 21:10:30 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 44
Xref: csiph.com microsoft.public.sqlserver.programming:31359

On Sunday, April 12, 2020 at 4:31:07 PM UTC-4, Erland Sommarskog wrote:
> John Hall (foundarecord@gmail.com) writes:
> > On Sunday, April 12, 2020 at 7:59:26 AM UTC-4, Erland Sommarskog wrote:
> >> 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