Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31360

Re: How do I order the columns on a pivot table?

Show all headers | View raw

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 #

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-11 09:08 -0700
  Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-12 13:59 +0200
    Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 12:02 -0700
      Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-12 21:32 +0200
      Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-15 12:54 -0700
        Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-16 20:02 +0200
    Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 12:27 -0700
    Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 12:43 -0700
      Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-12 22:31 +0200
        Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 14:10 -0700
          Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 14:14 -0700
          Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-12 23:15 +0200
        Re: How do I order the columns on a pivot table? John Hall <foundarecord@gmail.com> - 2020-04-12 14:18 -0700
          Re: How do I order the columns on a pivot table? Erland Sommarskog <esquel@sommarskog.se> - 2020-04-12 23:41 +0200

csiph-web