question

SriKotte-6865 avatar image
0 Votes"
SriKotte-6865 asked SriKotte-6865 answered

t-sql question

Hello all,

I am trying pivoting my query

I have a table with below columns

div_id primo md curve_name curve_value

10 1234 12 rop 45.6
10 1234 13 rop 43.5
10 1234 14 rop 40.6
10 1234 12 hook_load 11.2
10 1234 13 hook_load 12.3

I am trying to get the output like this

div_id primo md rop hook_load
10 1234 12 45.6 11.2
10 1234 13 43.5 12.3
10 1234 14 40.6 null

I am trying like this

SELECT
FROM (SELECT

FROM table123
WHERE primo = 1234
AND curve_name IN ('ROP',
'HOOK_LOAD')
)
PIVOT (MAX (curve_value)
FOR (curve_name)
IN ('ROP' AS rop,
'HOOK_LOAD' AS HOOK_LOAD))

I am getting the output as

div_id primo md rop hook_load

10 1234 12 45.6
10 1234 12 11.2
10 1234 13 43.5
10 1234 13 12.3

bottomline is my md is repeating..Could you please help where I am doing it wrong?

Thanks for the help

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @SriKotte-6865,

Please try:

 CREATE TABLE #test(div_id int,primo int,md int,curve_name varchar(25),curve_value decimal(3,1))
 INSERT INTO #test VALUES(10,1234,12,'rop',45.6),
 (10,1234,13,'rop',43.5),
 (10,1234,14,'rop',40.6),
 (10,1234,12,'hook_load',11.2),
 (10,1234,13,'hook_load',12.3)
    
 SELECT *
 FROM (SELECT div_id,primo,md,curve_name,curve_value 
 FROM #test
 WHERE primo = 1234
 AND curve_name IN ('ROP',
 'HOOK_LOAD')
 ) t
 PIVOT (MAX (curve_value)
 FOR curve_name
 IN (ROP,HOOK_LOAD)) p

Output:
128389-image.png

For more details, please refer to::
FROM - Using PIVOT and UNPIVOT

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (4.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SriKotte-6865 avatar image
0 Votes"
SriKotte-6865 answered

That worked.Thank you so much :)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.