asked
pivot in sql
I have table which has fields as follows ceofname ceolname executivefname executivelname i need a result of all the names fname lname which is the best possible way to get this .i need to query atleast 40 million records. thanks in advance
select ceofname as fname, ceolname as lname from table union all select executivefname, executivelname from table
A quick test, borrowing @ThomasRushton's code from below, shows that the union performed about 3 times better than the unpivot on a base (heap) table of 400,000 rows. On my laptop : IO 2 scans vs 5 scans : CPU 250ms vs 1300ms
pls excuse me for the questions. Is this the only way to do it ? or is it the best way ? I am worried because i have to read the 40 million record more than once to get the desired result will union all affect my performance??
yes this will do 2 table scans, but if this is how your data is stored and your output requirement is as stated, then I can't see any other way. If you have enough memory to hold the table in memory, you may see some gains there. If this is a common query/requirement, then consider storing the data in another table at the same time you insert/update the original table. The `union all` will affect your performance - it will be better than a `union` and the results will be accurate too.
thanks kev. I found the following article to do the same , and it works as well but i dont seem to understand how it works?
http://mangalpardeshi.blogspot.in/2009/04/unpivot-multiple-columns.html pls let me know the logic behind the following code. "WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1)"
That is using the number on the end of the column names to tie together the supplier with the city, so supplier**1** = city**1**, supplier**2** = city**2**
If you want to do it with an UNPIVOT, try this: DECLARE @ExecNames TABLE (ceofname VARCHAR(50), ceolname VARCHAR(50), executivefname VARCHAR(50), executivelname VARCHAR(50)) INSERT INTO @ExecNames VALUES ('John', 'Smith', 'Sam', 'Brown'), ('Fred', 'Brubeck', 'Dave', 'Smith') SELECT FirstName, LastName FROM ( SELECT ceofname, ceolname, executivefname, executivelname FROM @ExecNames) pvt UNPIVOT (FirstName FOR FirstNames IN (ceofname, executivefname)) FName UNPIVOT (LastName FOR LastNames IN (ceolname, executivelname)) LName WHERE ( (FirstNames = 'ceofname' AND LastNames = 'ceolname') OR (FirstNames = 'executivefname' AND LastNames = 'executivelname') ) (Ignore the first couple of lines, and replace @ExecNames with the name of your table...)
10 People are following this question.
