Results 1 to 2 of 2

Thread: Unpivot data

  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Unpivot data

    How do I unpivot this data in sql 2000?



    Code:
    Select date, COUNT (CASE WHEN DeviceCode LIKE '%1231223%'
    		    OR	 DeviceCode LIKE '%54542%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW TEN',
           COUNT (CASE WHEN DeviceCode LIKE '%123555%'
    		    OR	 DeviceCode LIKE '%756543%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW FIF',
           COUNT (CASE WHEN DeviceCode LIKE '%123123'
    		    OR	 DeviceCode LIKE '%4567u%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW TWENTY',
           COUNT (CASE WHEN DeviceCode LIKE '%2344234%'
                        OR	 DeviceCode LIKE '%23622%'
    		    OR	 DeviceCode LIKE '%2622222%'
    		    OR	 DeviceCode LIKE '%11111111%'
    		    OR	 DeviceCode LIKE '%244311%'
    		    OR	 DeviceCode LIKE '%1123123%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW BLACK',
    	 COUNT (CASE WHEN DeviceCode LIKE '%1236675%'
    		    OR	 DeviceCode LIKE '%58757%'
    		    OR	 DeviceCode LIKE '%2433242%'
    			THEN DeviceCode 
                        END) AS 'OUT NEW CAST',
    	 COUNT (CASE WHEN DeviceCode LIKE '%345213%'
    		    OR	 DeviceCode LIKE '%2356343%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW TW MB',
    	 COUNT (CASE WHEN DeviceCode LIKE '%1235211%'
    		    OR	 DeviceCode LIKE '%1236754%'
    		    THEN DeviceCode 
                        END) AS 'OUT NEW SEVEN MB'
    FROM 	History
    WHERE 	date 	between '20100201' and '20100301'
    AND 	State IN ('Great', 'Good')
    AND Code IN ('214', '74465', '45676', '546456', '456546', '456654', '45663', 'VW117','21123','78987','45677', '97523')

    So instead of it look like:

    date OUT NEW TEN OUT NEW FIF
    20100201 0 0
    20100202 0 0
    20100203 0 0
    20100204 0 0
    20100205 0 0
    20100208 0 0
    20100209 0 0
    20100211 2 0

    it would look like

    date Count Results
    20100201 0 OUT NEW TEN
    20100202 0 OUT NEW TEN
    20100203 0 OUT NEW TEN
    20100204 0 OUT NEW TEN
    20100205 0 OUT NEW TEN
    20100208 0 OUT NEW TEN
    20100209 0 OUT NEW TEN
    20100211 2 OUT NEW TEN
    20100201 0 OUT NEW FIF
    20100202 0 OUT NEW FIF
    20100203 0 OUT NEW FIF
    20100204 0 OUT NEW FIF
    20100205 0 OUT NEW FIF
    20100208 0 OUT NEW FIF
    20100209 0 OUT NEW FIF
    20100211 0 OUT NEW FIF
    20100201 0 OUT NEW Twenty
    20100202 0 OUT NEW Twenty
    20100203 0 OUT NEW Twenty
    20100204 0 OUT NEW Twenty
    20100205 0 OUT NEW Twenty
    20100208 0 OUT NEW Twenty
    20100209 0 OUT NEW Twenty
    20100211 0 OUT NEW Twenty

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Tou can try the following.

    Select date, COUNT(*) AS [Count],
    CASE WHEN DeviceCode LIKE '%1231223%'
    OR DeviceCode LIKE '%54542%'
    THEN 'OUT NEW TEN' ELSE
    CASE WHEN DeviceCode LIKE '%123555%'
    OR DeviceCode LIKE '%756543%'
    THEN 'OUT NEW FIF' ELSE
    CASE WHEN DeviceCode LIKE '%123123'
    OR DeviceCode LIKE '%4567u%'
    THEN 'OUT NEW TWENTY' ELSE
    WHEN DeviceCode LIKE '%2344234%'
    OR DeviceCode LIKE '%23622%'
    OR DeviceCode LIKE '%2622222%'
    OR DeviceCode LIKE '%11111111%'
    OR DeviceCode LIKE '%244311%'
    OR DeviceCode LIKE '%1123123%'
    THEN 'OUT NEW BLACK' ELSE
    WHEN DeviceCode LIKE '%1236675%'
    OR DeviceCode LIKE '%58757%'
    OR DeviceCode LIKE '%2433242%'
    THEN 'OUT NEW CAST' ELSE
    WHEN DeviceCode LIKE '%345213%'
    OR DeviceCode LIKE '%2356343%'
    THEN 'OUT NEW TW MB' ELSE
    WHEN DeviceCode LIKE '%1235211%'
    OR DeviceCode LIKE '%1236754%'
    THEN 'OUT NEW SEVEN MB'
    END
    END
    END
    END
    END
    END
    END AS Result
    FROM History
    WHERE date between '20100201' and '20100301'
    AND State IN ('Great', 'Good')
    AND Code IN ('214', '74465', '45676', '546456', '456546', '456654', '45663', 'VW117','21123','78987','45677', '97523')
    GROUP BY date,
    CASE WHEN DeviceCode LIKE '%1231223%'
    OR DeviceCode LIKE '%54542%'
    THEN 'OUT NEW TEN' ELSE
    CASE WHEN DeviceCode LIKE '%123555%'
    OR DeviceCode LIKE '%756543%'
    THEN 'OUT NEW FIF' ELSE
    CASE WHEN DeviceCode LIKE '%123123'
    OR DeviceCode LIKE '%4567u%'
    THEN 'OUT NEW TWENTY' ELSE
    WHEN DeviceCode LIKE '%2344234%'
    OR DeviceCode LIKE '%23622%'
    OR DeviceCode LIKE '%2622222%'
    OR DeviceCode LIKE '%11111111%'
    OR DeviceCode LIKE '%244311%'
    OR DeviceCode LIKE '%1123123%'
    THEN 'OUT NEW BLACK' ELSE
    WHEN DeviceCode LIKE '%1236675%'
    OR DeviceCode LIKE '%58757%'
    OR DeviceCode LIKE '%2433242%'
    THEN 'OUT NEW CAST' ELSE
    WHEN DeviceCode LIKE '%345213%'
    OR DeviceCode LIKE '%2356343%'
    THEN 'OUT NEW TW MB' ELSE
    WHEN DeviceCode LIKE '%1235211%'
    OR DeviceCode LIKE '%1236754%'
    THEN 'OUT NEW SEVEN MB'
    END
    END
    END
    END
    END
    END
    END
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •