Advanced Search

Return to Snippet

Revision: 49909
at August 3, 2011 15:09 by jimfred
Updated Code 
-- Using UNPIVOT    
SELECT K 
	, PortName
	, PortOk
FROM ( SELECT K, North, South, East, West FROM dbo.tblPorts ) AS T
UNPIVOT ( PortOk FOR PortName IN (North, South, East, West )) AS U
	
		

-- Using CROSS JOIN UNION		
SELECT 
	T.K,
	U.PortName,
	PortOk = 
		CASE U.PortName
			WHEN 'North' THEN T.North
			WHEN 'South' THEN T.South
			WHEN 'East' THEN T.East
			WHEN 'West' THEN T.West
		END 
FROM
	dbo.tblPorts AS T
	CROSS JOIN (SELECT 'North' UNION ALL SELECT 'South' UNION ALL SELECT 'East' UNION ALL SELECT 'West' ) AS U (PortName)

Revision: 49908
at August 2, 2011 05:59 by jimfred
Initial Code 
select 
	T.k,
	Port = V.Port,
	PortOk = 
		CASE V.Port
		WHEN 0 THEN T.North
		WHEN 1 THEN T.South
		WHEN 2 THEN T.East
		WHEN 3 THEN T.West
		END -- CASE
FROM
	dbo.tblPorts T
	CROSS JOIN (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) V (Port)
Initial URL 
http://stackoverflow.com/questions/3678479/split-multiple-columns-into-multiple-rows
Initial Description 
<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>
Initial Title 
split multiple columns into multiple rows - UNPIVOT
Initial Tags 

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL