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

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

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

Show all headers | View raw

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

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