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
  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.