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

Groups > microsoft.public.sqlserver.programming > #31351 > unrolled thread

How do I order the columns on a pivot table?

Started byJohn Hall <foundarecord@gmail.com>
First post2020-04-11 09:08 -0700
Last post2020-04-12 23:41 +0200
Articles 14 — 2 participants

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  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

#31351 — How do I order the columns on a pivot table?

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-11 09:08 -0700
SubjectHow do I order the columns on a pivot table?
Message-ID<32b39068-acc8-48ef-ac56-7f80bc2ee65b@googlegroups.com>
I have a data table with these 3 columns
SELECT [TestID],[ParameterID],[TextValue]  FROM [ParameterEntries]
And a setup table with these 3 columns
SELECT [PartID], [ParameterID], [PresentOrder]  FROM [ParametersUsed]

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 = (SELECT TOP 1 TestID FROM Subgroups WHERE CharID = 502107)
) p
pivot(max(TextValue) for rnk in ([1], [2], [3], [4], [5])) piv

TestID  Param1          Param2  Param3  Param4  Param5
506789  20147338        3w        2.14  1.49    1.73

SELECT [PartID], [ParameterID], [PresentOrder]
  FROM [ParametersUsed]
  WHERE PartID = (SELECT PartID FROM CharList WHERE CharID = 502107)
  ORDER BY PresentOrder

PartID  ParameterID  PresentOrder
502014        154             1
502014        502009          2
502014        502022          3
502014        502023          4
502014        502024          5

How do I put the Param1, Param2, Param3... in PresentOrder?

[toc] | [next] | [standalone]

#31353

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-12 13:59 +0200
Message-ID<XnsAB9D8E503D7CEYazorman@127.0.0.1>
In reply to#31351
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

[toc] | [prev] | [next] | [standalone]

#31354

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 12:02 -0700
Message-ID<1190dedf-2738-452d-bb11-23ad74088e91@googlegroups.com>
In reply to#31353
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

ParametersUsed does not have a TestID column.  But, I think I need to create a recordset from a join of ParametersUsed and ParameterEntries PU.ParameterId = PE.ParameterID

[toc] | [prev] | [next] | [standalone]

#31356

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-12 21:32 +0200
Message-ID<XnsAB9DDB16AF1F7Yazorman@127.0.0.1>
In reply to#31354
John Hall (foundarecord@gmail.com) writes:
> ParametersUsed does not have a TestID column.  But, I think I need to
> create a recordset from a join of ParametersUsed and ParameterEntries
> PU.ParameterId = PE.ParameterID 
>

I got the impression that it had a TestID from your one of your posts.

> So, why is Param1 getting duplicated into Param2 but 3, 4, & 5 are fine?
>

For this type of problem, it often helps if you post CREATE TABLE statements 
for your tables, INSERT statements with sample data, enough to illustrate 
all angles of the problem. This makes it simple to copy and paste into a 
query window to develop a tested solution. Don't forget to say which version 
SQL Server you are using.

[toc] | [prev] | [next] | [standalone]

#31364

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-15 12:54 -0700
Message-ID<901f6665-b649-4e88-a0ac-d217996f1a35@googlegroups.com>
In reply to#31354
On Sunday, April 12, 2020 at 3:02:55 PM UTC-4, John Hall wrote:
> 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
> 
> ParametersUsed does not have a TestID column.  But, I think I need to create a recordset from a join of ParametersUsed and ParameterEntries PU.ParameterId = PE.ParameterID

It was strange.  I replaced the TestID with ParameterID, but the 1 PE.TextValue duplicated into the 2nd column.??? but, the 3rd, 4th and 5th columns were correct. I changed from MIN to MAX and fixed that problem. 

RESOLVED!!!

[toc] | [prev] | [next] | [standalone]

#31365

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-16 20:02 +0200
Message-ID<XnsABA1CBCF98D55Yazorman@127.0.0.1>
In reply to#31364
John Hall (foundarecord@gmail.com) writes:
> It was strange.  I replaced the TestID with ParameterID, but the 1
> PE.TextValue duplicated into the 2nd column.??? but, the 3rd, 4th and
> 5th columns were correct. I changed from MIN to MAX and fixed that
> problem. 
> 


MIN or MAX should not have any importance if it is a proper pivot. But
since you never gave me the exact table defintions, all I could offer was
guesses anyway.

[toc] | [prev] | [next] | [standalone]

#31355

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 12:27 -0700
Message-ID<e6e7c619-b673-4aec-b915-3d02be7ede67@googlegroups.com>
In reply to#31353
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

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 TestID FROM Subgroups WHERE CharID = 502107 AND DateTime = '08/29/2017 10:29:25' )
GROUP  BY PE.TestID
TestID  Param1          Param2          Param3  Param4  Param5
519802	201487332	201487332	2.20	1.54	1.80

SELECT ParameterID, TextValue FROM ParameterEntries WHERE TestID = 519802
154	201487332
502009	5w
502022	2.20
502023	1.54
502024	1.80

So, why is Param1 getting duplicated into Param2 but 3, 4, & 5 are fine?

[toc] | [prev] | [next] | [standalone]

#31357

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 12:43 -0700
Message-ID<7a25939e-1e2d-4de3-9315-edef91428a1a@googlegroups.com>
In reply to#31353
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.

[toc] | [prev] | [next] | [standalone]

#31358

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-12 22:31 +0200
Message-ID<XnsAB9DE50FF9274Yazorman@127.0.0.1>
In reply to#31357
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.

[toc] | [prev] | [next] | [standalone]

#31359

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 14:10 -0700
Message-ID<bba1f50c-e0ae-4466-a0fb-fee3ef82c465@googlegroups.com>
In reply to#31358
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

[toc] | [prev] | [next] | [standalone]

#31360

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 14:14 -0700
Message-ID<30a98e80-d3a0-4689-b650-ddc1007677db@googlegroups.com>
In reply to#31359
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 #

[toc] | [prev] | [next] | [standalone]

#31361

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-12 23:15 +0200
Message-ID<XnsAB9DECA314306Yazorman@127.0.0.1>
In reply to#31359
John Hall (foundarecord@gmail.com) writes:
> I want to replace the ParameterEntries query with one that orders the
> TextValues in the order of their ParameterIDs in PresentOrder in
> ParametersUsed 
> 


Please post CREATE TABLE statements for your tables and INSERT statements 
with sample data, enough to illustrate all angles of the problem, and the 
expected results given the query. I can't work from your query. It's using 
the PIVOT keyword (which I never bothered to learn) and the RIGHT JOIN 
operator (which gives me a headache).

[toc] | [prev] | [next] | [standalone]

#31362

FromJohn Hall <foundarecord@gmail.com>
Date2020-04-12 14:18 -0700
Message-ID<3e7cf417-607c-4659-aed2-d4c4668e592b@googlegroups.com>
In reply to#31358
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

[toc] | [prev] | [next] | [standalone]

#31363

FromErland Sommarskog <esquel@sommarskog.se>
Date2020-04-12 23:41 +0200
Message-ID<XnsAB9DF1019BAFYazorman@127.0.0.1>
In reply to#31362
John Hall (foundarecord@gmail.com) writes:
> This time it didn't give an error but, returned NULL for all ParamX values 
> and incorrect DataValues
> 

Please refer to my previous post. I don't know your data and table, and I 
am not inclined to make further gueses.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web