Pivot table with IN operator and WHERE clause

Question

i'm working on SQL query and using operators PIVOT, IN and clause WHERE. When I finished, noticed that my SQL script sort some column(in that case column ANGLE_1) in a different way, help me out please to find where is the problem.

SQL query:

SELECT *
FROM (
SELECT 
      row_number() over (Partition By [PARAMETER_NAME] Order by [PARAMETER_VALUE]) as new 
      ,[PARAMETER_NAME]
      ,[PARAMETER_VALUE]

  FROM [dbo].table
  where [EXCHANGE_HEADER_ID] in ('1', '2')
  )  
  as s 
  pivot ( 
         min([PARAMETER_VALUE])
         FOR [PARAMETER_NAME] IN  (  DMX
                                    ,TYPE
                                    ,UNBALANCE_1
                                    ,ANGLE_1
                                    ,UNBALANCE_2
                                    ,ANGLE_2
                                    ,STATUS 
 ) )AS PVT

Table [dbo].table:

ID  CREATED PARAMETER_NAME  PARAMETER_VALUE EXCHANGE_HEADER_ID
1   2016-09-09 11:39:21.3927453 TYPE    TYPE 3  1
2   2016-09-09 11:39:21.3927453 UNBALANCE_1 0.094   1
3   2016-09-09 11:39:21.3927453 ANGLE_1 95.7    1
4   2016-09-09 11:39:21.3927453 UNBALANCE_2 0.195   1
5   2016-09-09 11:39:21.3927453 ANGLE_2 135.8   1
6   2016-09-09 11:39:21.3927453 STATUS  0   1
7   2016-09-09 11:39:21.3927453 DMX 75044500454391206509-A1625300635    1
8   2016-09-09 11:39:21.3927453 MACHINE 442002  1
9   2016-09-09 11:39:21.3927453 DATE    2016-09-09 11:39:13 1
10  2016-09-09 23:20:27.2773565 TYPE    TYPE 3  2
11  2016-09-09 23:20:27.2773565 UNBALANCE_1 0.826   2
12  2016-09-09 23:20:27.2773565 ANGLE_1 229.6   2
13  2016-09-09 23:20:27.2773565 UNBALANCE_2 0.835   2
14  2016-09-09 23:20:27.2773565 ANGLE_2 198.1   2
15  2016-09-09 23:20:27.2773565 STATUS  0   2
16  2016-09-09 23:20:27.2773565 DMX 75044500454391206509-A1625301504    2
17  2016-09-09 23:20:27.2773565 MACHINE 442002  2
18  2016-09-09 23:20:27.2773565 DATE    2016-09-09 23:20:02 2

Output:

new DMX     TYPE    UNBALANCE_1 ANGLE_1 UNBALANCE_2 ANGLE_2 STATUS
1   750..   TYPE 3  0.094           229.6   0.195   135.8   0
2   750..   TYPE 3  0.826           95.7    0.835   198.1   0

Expected output:

new DMX     TYPE    UNBALANCE_1 ANGLE_1 UNBALANCE_2 ANGLE_2 STATUS
1   750..   TYPE 3  0.094           95.7    0.195   135.8   0
2   750..   TYPE 3  0.826           229.6   0.835   198.1   0

As you see above, the problem is in ANGLE_1 that contains the wrong value, probably it's because of additional column row_number() over (Partition By [PARAMETER_NAME] Order by [PARAMETER_VALUE]) as new ?. Thank you for your help

score 1 · Accepted Answer · answered May 13 '19 at 12:39

The ordering also relies on the [ID] value and not just the parameter_value. This means that Angle_1 relates to Unbalance_1. I hope this helps.

select * into #table from
( select * from (values
(1,   '2016-09-09 11:39:21.3927453', 'TYPE',    'TYPE 3',  1),
(2,   '2016-09-09 11:39:21.3927453', 'UNBALANCE_1', '0.094',   1),
(3,   '2016-09-09 11:39:21.3927453', 'ANGLE_1', '95.7',    1),
(4,   '2016-09-09 11:39:21.3927453', 'UNBALANCE_2', '0.195',   1),
(5,   '2016-09-09 11:39:21.3927453', 'ANGLE_2', '135.8',   1),
(6,   '2016-09-09 11:39:21.3927453', 'STATUS',  '0',   1),
(7,   '2016-09-09 11:39:21.3927453', 'DMX', '75044500454391206509-A1625300635',    1),
(8,   '2016-09-09 11:39:21.3927453', 'MACHINE', '442002',  1),
(9,   '2016-09-09 11:39:21.3927453', 'DATE',    '2016-09-09 11:39:13', 1),
(10,  '2016-09-09 23:20:27.2773565', 'TYPE',    'TYPE 3',  2),
(11,  '2016-09-09 23:20:27.2773565', 'UNBALANCE_1', '0.826',   2),
(12,  '2016-09-09 23:20:27.2773565', 'ANGLE_1', '229.6',   2),
(13,  '2016-09-09 23:20:27.2773565', 'UNBALANCE_2', '0.835',   2),
(14,  '2016-09-09 23:20:27.2773565', 'ANGLE_2', '198.1',   2),
(15,  '2016-09-09 23:20:27.2773565', 'STATUS',  '0',   2),
(16,  '2016-09-09 23:20:27.2773565', 'DMX', '75044500454391206509-A1625301504',    2),
(17,  '2016-09-09 23:20:27.2773565', 'MACHINE', '442002',  2),
(18,  '2016-09-09 23:20:27.2773565', 'DATE',    '2016-09-09 23:20:02', 2)) tabledata (ID,  CREATED, PARAMETER_NAME,  PARAMETER_VALUE, EXCHANGE_HEADER_ID)
) x

SELECT *
FROM (
SELECT 
      row_number() over (Partition By [PARAMETER_NAME] Order by [ID] asc, [PARAMETER_VALUE] desc) as new 
      ,[PARAMETER_NAME]
      ,[PARAMETER_VALUE]

  FROM #table
  where [EXCHANGE_HEADER_ID] in ('1', '2')
  )  
  as s 
  pivot ( 
         min([PARAMETER_VALUE])
         FOR [PARAMETER_NAME] IN  (  DMX
                                    ,TYPE
                                    ,UNBALANCE_1
                                    ,ANGLE_1
                                    ,UNBALANCE_2
                                    ,ANGLE_2
                                    ,STATUS 
 ) )AS PVT;



 drop table #table;

Pivot table with IN operator and WHERE clause

1 Answers1