0

I have a schema with the following columns and rows from [dbo].[obs]

NIN SECTION RESULT
CM86029 Reg X00
CM86029 EmNumber 807032
CM86029 Employee PETER
CM84038 Reg X01
CM84038 EmpNumber 807033
CM84038 Employee EMMANUEL

i have tried to get the contents in the section pickup values from the Result column to get the following ouput using unpivot query.

NIN Reg EmNumber Employee
CM86029 X00 807032 PETER
CM84038 X01 807033 EMMANUEL

the result i have got are all different from what i need to achieve. below is my code.

SELECT    NIN, RegistrationNumber,  EmployeeNumber, SupplierNumber
FROM      [dbo].[obs]
CROSS APPLY
(
  VALUES
        ('RegistrationNumber','EmployeeNumber','SupplierNumber','ss'),

         (Result,Result,Result,Result) 

 ) c (RegistrationNumber,EmployeeNumber,SupplierNumber,value);

This is the output i get instead.

NIN Reg EmNumber Employee
CM860291035WVE RegistrationNumber EmployeeNumber SupplierNumber
CM860291035WVE Teachers/Doctors Teachers/Doctors
CM860291035WVE RegistrationNumber EmployeeNumber SupplierNumber
CM860291035WVE 000000000807032 000000000807032 000000000807032
CM860291035WVE RegistrationNumber EmployeeNumber SupplierNumber
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Miiro Bels
  • 27
  • 2
  • You want to *pivot*, not unpivot. – GMB Apr 28 '23 at 21:54
  • how tho, having no field to SUM() – Miiro Bels Apr 28 '23 at 21:57
  • Then you would use expressions like `MAX(CASE WHEN section = 'EmNumber' THEN result END)`. You can check the link I closed your question with for a lot of examples. If you get stuck somewhere while implementing the new solution, please come back :) – GMB Apr 28 '23 at 22:04

0 Answers0