Advanced Search

split multiple columns into multiple rows - UNPIVOT

/ Published in: SQL
Save to your folder(s)
<p>UNPIVOT a table to create more rows using two approaches</p>

<p>Convert this...</p>

<pre>
K North South East West
0 1 0 0 0
1 0 1 0 0
2 0 0 1 0
3 0 0 0 1
</pre>

<p>... to this...</p>

<pre>
K PortName PortOk
0 North 1
0 South 0
0 East 0
0 West 0
1 North 0
1 South 1
1 East 0
1 West 0
2 North 0
2 South 0
2 East 1
2 West 0
3 North 0
3 South 0
3 East 0
3 West 1
</pre>

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. -- Using UNPIVOT    
  2. SELECT K 
  3. 	, PortName
  4. 	, PortOk
  5. FROM ( SELECT K, North, South, East, West FROM dbo.tblPorts ) AS T
  6. UNPIVOT ( PortOk FOR PortName IN (North, South, East, West )) AS U
  7.  
  8.  
  9.  
  10. -- Using CROSS JOIN UNION		
  11. SELECT 
  12. 	T.K,
  13. 	U.PortName,
  14. 	PortOk = 
  15. 		CASE U.PortName
  16. 			WHEN 'North' THEN T.North
  17. 			WHEN 'South' THEN T.South
  18. 			WHEN 'East' THEN T.East
  19. 			WHEN 'West' THEN T.West
  20. 		END 
  21. FROM
  22. 	dbo.tblPorts AS T
  23. 	CROSS JOIN (SELECT 'North' UNION ALL SELECT 'South' UNION ALL SELECT 'East' UNION ALL SELECT 'West' ) AS U (PortName)

URL: http://stackoverflow.com/questions/3678479/split-multiple-columns-into-multiple-rows

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.