There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Convert Columns to Rows with a View
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.
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.
Who is Participating?
You can use dynamic sql to return different columns for each batch
CREATE TABLE #t (Batch int, Element varchar(20), Value int)
INSERT #t --Insert test data
SELECT 1,'Carbon',52
UNION ALL SELECT 1,'Oxy',12
UNION ALL SELECT 1,'Nit',14
UNION ALL SELECT 2,'Carbon',33
UNION ALL SELECT 2,'Oxy',5
DECLARE @batch int --batch number to query
SET @batch=2
DECLARE @elements varchar(max) --list of elements for batch
SET @elements= STUFF((SELECT ','+QUOTENAME(Element) FROM #t WHERE Batch=@batch FOR XML PATH ('')),1,1,'')
DECLARE @sql varchar(max) --dynamic sql statement
SET @sql =
'SELECT *
FROM #t
PIVOT (SUM(Value) FOR Element IN (' + @elements + ')) pvt
WHERE Batch=' + CAST(@batch as varchar)
print @sql --print the sql statement for debugging
exec (@sql)
GO
DROP TABLE #t --drop test table
I figured, but it is good you showed it in case the view is not really a hard requirement. I was just pointing that out before getting the post back that it errored out in the view. Which would be interesting if the ERP application could take variable columns and handle well versus fixed number with 0's when not applicable. I had one system like that where you can store variable length fields -- those are pain to do reporting on -- well if you have users who are very inconsistent about where they put data as I would always get one value of "." because they did not want to fill in the field, so now it thinks there are multiple values in the field. *sigh* Anyway, I digress. Hopefully, this is helpful to sqlguy29!
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month7 days, 18 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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!