Solved

Convert Columns to Rows with a View

Posted on 2011-09-30
6
195 Views
Last Modified: 2012-05-12
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.  


0
Comment
Question by:sqlguy29
[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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36894990
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!
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36895325
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

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36895331
You cannot use dynamic SQL in a view. ;)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 22

Expert Comment

by:Thomasian
ID: 36895334
aah, I missed that part. :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36895364
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!
0
 

Author Closing Comment

by:sqlguy29
ID: 37031649
I didnt use this solution be appreciate the effort.   I just split the points. Thanks guys
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 55
SQL - Aging Report - Display Months with no data 8 43
invoke-sqlcmd help 5 33
SQL Server Pivot 5 42
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question