Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Convert Columns to Rows with a View
Posted on 2011-09-30
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2
6 Comments
Active today
Hi.
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!
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!
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
Active today
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!
Featured Post
How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month7 days, 3 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.