Hello all,
I have a table with this data:
Batch Element Value
1 Carbon 52
1 Oxy 12
1 Nit 14
2 Carbon 33
2 Oxy 5
What I need is a View that will convert the data to:
For Batch 1
Batch Carbon Oxy Nit
1 52 12 14
For Batch 2
Batch Carbon Oxy
2 33 5
I can only link my data in my ERP system to an SQL View.
My problem is that with different batchs, different elements are measured. There are about 7000 Batches. I cant return Elements with Null values. I have to return integers. I can limit the number of columns to about 30. The ERP System can be most difficult. What makes this worse is that its an Industry Specific Quality Document.
To get the results in a view I am thinking maybe I would need to use an SQL Function. Then I worry about performance. I have thought about this for two years and I am clueless where to start. thanks in advance.
MS SQL 2005 and higher has e PIVOT keyword that may help with this. The caveat is that it requires a finite, static lost of data values to pivot on. It appears that is the case you have here, so you can give that a try.
The basic syntax is:
SELECT *
FROM your_table
PIVOT (SUM(Value) FOR Element IN (Carbon, Oxy, Nit)) pvt
;
You can explicitly define the columns in the select statement, so you can do COALESE(Carbon, 0) so you can get zeros instead of NULL.
Hope that helps!