t-sql question

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

Answer 1

Hi @Sri Kotte ,

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:

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.

Answer 2

That worked.Thank you so much :)