X-Received: by 2002:a37:4c0a:: with SMTP id z10mr14019698qka.408.1586726074979; Sun, 12 Apr 2020 14:14:34 -0700 (PDT) X-Received: by 2002:a05:620a:16cc:: with SMTP id a12mr13623314qkn.340.1586726074786; Sun, 12 Apr 2020 14:14:34 -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:14:34 -0700 (PDT) In-Reply-To: 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> <7a25939e-1e2d-4de3-9315-edef91428a1a@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <30a98e80-d3a0-4689-b650-ddc1007677db@googlegroups.com> Subject: Re: How do I order the columns on a pivot table? From: John Hall Injection-Date: Sun, 12 Apr 2020 21:14:34 +0000 Content-Type: text/plain; charset="UTF-8" Lines: 47 Xref: csiph.com microsoft.public.sqlserver.programming:31360 On Sunday, April 12, 2020 at 5:10:33 PM UTC-4, John Hall wrote: > 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 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 #