X-Received: by 2002:a0c:ed4c:: with SMTP id v12mr15056893qvq.120.1586726298919; Sun, 12 Apr 2020 14:18:18 -0700 (PDT)
X-Received: by 2002:a37:9e08:: with SMTP id h8mr6738357qke.369.1586726298735; Sun, 12 Apr 2020 14:18:18 -0700 (PDT)
Path: csiph.com!xmission!news.snarked.org!border2.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:18:18 -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: <3e7cf417-607c-4659-aed2-d4c4668e592b@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:18:18 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 48
Xref: csiph.com microsoft.public.sqlserver.programming:31362

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