advanced code snippet search

Posted By

jimfred on 08/02/11


Tagged

UNPIVOT


Versions (?)

split multiple columns into multiple rows - UNPIVOT

 / Published in: SQL
 

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

UNPIVOT a table to create more rows using two approaches

Convert this...

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

... to this...

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
Expand | Embed | Plain Text 
  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)

Report this snippet  

You need to login to post a comment.