question

aRookieBIdev avatar image
aRookieBIdev 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
sql-server-2008pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
select ceofname as fname, ceolname as lname from table union all select executivefname, executivelname from table
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
2 Likes 2 ·
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??
0 Likes 0 ·
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.
0 Likes 0 ·
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)"
0 Likes 0 ·
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**
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
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 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.