June 29, 2011 at 3:20 am
need a query / procedure which would convert Columns into Rows without causing any damages to the original data.
Table#1
Job Name1_1 name1_2 name1_2 name1_4 city1_1 city1_2 city1_3 city1_4 phone1_1 phone1_2 phoen1_3 phone1_4
XYZ aaaa bbbb cccc dddd abcd bcde cdef defg 1111 2222 3333 4444
output table as:
Job name city phone
XYZ aaaa abcd 1111
XYZ bbbb bcde 2222
XYZ cccc cdef 3333
XYZ dddd defg 4444
Please help .
June 29, 2011 at 4:17 am
Hi
I came across a previous post i was working through which might be able to help you
http://www.sqlservercentral.com/Forums/Topic263272-8-1.aspx
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 29, 2011 at 4:19 am
Use UNPIVOT operator.
e.g.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) AS p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
Abhijit - http://abhijitmore.wordpress.com
June 29, 2011 at 7:02 am
Abhijit More (6/29/2011)
Use UNPIVOT operator.
The UNPIVOT operator doesn't work well when you want to unpivot multiple sets of data. Here the OP wants to unpivot three sets of data: name, city, and phone.
Your best bet in this case is to use the UNION (ALL)
SELECT Job, Name1 AS [Name], City1 AS City, Phone1 AS Phone
FROM YourTable
UNION ALL
SELECT Job, Name2 AS [Name], City2 AS City, Phone2 AS Phone
FROM YourTable
UNION ALL
SELECT Job, Name3 AS [Name], City3 AS City, Phone3 AS Phone
FROM YourTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2011 at 7:05 am
Hi asranantha
can you provide the sample data so that I can show UNPIVOT example.
Abhijit - http://abhijitmore.wordpress.com
June 29, 2011 at 9:05 am
Here is why UNPIVOT doesn't work with multiple sets of data.
Suppose you have the row:
JOB Name1 Name2 City1 City2
ABC Smith Jones NYC Chicago
The names are matched to the cities by their position. Name1 corresponds to City1 and Name2 corresponds to City2. The UNPIVOT operator collapses the specified columns into a single column and passes the other columns through unchanged. So say we UNPIVOT on name first. We now have the intermediate result:
JOB Name City1 City2
ABC Smith NYC Chicago
ABC Jones NYC Chicago
But you'll notice that this destroys the positional information that we were using to match the name to the city, so we can no longer match the name to the city.
In order to get UNPIVOT to work with multiple sets, we need to somehow pack the related columns into a single unit, unpivot on that unit, and then unpack the results of the unpivot. It doesn't matter which method you use to pack/unpack the columns (e.g., concatenation, xml, etc.), it is going to add overhead that you simply don't have with the UNION (ALL) option.
In short, you can use UNPIVOT, but it's very messy when you start dealing with multiple sets.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2011 at 11:04 am
Itzik has it down, use the APPLY operator for this. UNION ALL will scan the data multiple times, APPLY will only scan it once, and it's cleaner than the CROSS JOIN. The article is a good read, skip to page 2 to cut right to the APPLY section and his conclusion:
http://www.sqlmag.com/article/database-administration/unpivoting-data
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 11:33 am
opc.three (6/29/2011)
Itzik has it down, use the APPLY operator for this. UNION ALL will scan the data multiple times, APPLY will only scan it once, and it's cleaner than the CROSS JOIN. The article is a good read, skip to page 2 to cut right to the APPLY section and his conclusion:
The VALUES syntax that he uses in his APPLY example was introduced in SQL 2008, so you will still need to use UNION (ALL), but you can use it in the APPLY to prevent the multiple scans.
SELECT Job
FROM YourTable
CROSS APPLY (
SELECT Name1, City1, Phone1
UNION ALL
SELECT Name2, City2, Phone2
) AS unpvt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2011 at 11:52 am
The two usages of UNION ALL you're comparing a VERY different.
UNION ALL used how it was shown earlier in the thread will cause multiple scans of the data.
UNION ALL used how it was just shown in your post (as a 2005-compliant row constructor equivalent to VALUES() ) will allow you to use the APPLY technique in 2005 and keep it down to one scan of the data.
i.e. Using Itzik's examples these produce equivalent execution plans:
-- Listing 4: Unpivot with cross join
WITH C AS (
SELECT shipperid,
shipcity,
CASE shipcity
WHEN 'Barcelona' THEN frtBarcelona
WHEN 'Madrid' THEN frtMadrid
WHEN 'Sevilla' THEN frtSevilla
END AS freight
FROM dbo.PvtSample
CROSS JOIN ( VALUES ( 'Barcelona'),
( 'Madrid'),
( 'Sevilla') ) AS SC (shipcity)
)
SELECT *
FROM C
WHERE freight IS NOT NULL ;
-- Listing 4: Unpivot with cross join (modified for 2005-compliance, use UNION ALL instead of VALUES)
WITH C AS (
SELECT shipperid,
shipcity,
CASE shipcity
WHEN 'Barcelona' THEN frtBarcelona
WHEN 'Madrid' THEN frtMadrid
WHEN 'Sevilla' THEN frtSevilla
END AS freight
FROM dbo.PvtSample
CROSS JOIN (
SELECT 'Barcelona'
UNION ALL
SELECT 'Madrid'
UNION ALL
SELECT 'Sevilla'
) AS SC (shipcity)
)
SELECT *
FROM C
WHERE freight IS NOT NULL ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 12:24 pm
opc.three (6/29/2011)
UNION ALL used how it was just shown in your post (as a 2005-compliant row constructor equivalent to VALUES() ) will allow you to use the APPLY technique in 2005 and keep it down to one scan of the data.
Isn't that what I just said?
drew.allen (6/29/2011)
you can use it in the APPLY to prevent the multiple scans.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2011 at 12:29 pm
drew.allen (6/29/2011)
opc.three (6/29/2011)
UNION ALL used how it was just shown in your post (as a 2005-compliant row constructor equivalent to VALUES() ) will allow you to use the APPLY technique in 2005 and keep it down to one scan of the data.Isn't that what I just said?
drew.allen (6/29/2011)
you can use it in the APPLY to prevent the multiple scans.Drew
Eek, sorry, I misread. I thought you were still trying to compare the earlier usage of UNION ALL with the one that acts as a row constructor. As long as we land on APPLY we're good 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply