f7e81c44   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Wed, 2 Nov 2011 13:15:30 -0700 (PDT), srgebauer@gmail.com wrote:   
      
   >I have a two tables, Stock and Bin, with Stock having one to Bin's many.   
   Stock has  fields Number and Desc1 and Bin has Number, Warehouse, and Units. I   
   would like to join Stock and Bin on Number and use the value in Bin.warehouse   
   as a field name with    
   Bin.units as the value.   
   >   
   >For Example:   
   >Stock table   
   >Number....Desc1   
   >100.....WidgetA   
   >110.....WidgetB   
   >   
   >Bin Table   
   >Number...Warehouse...Units   
   >100...NCWARE...10   
   >100...PAWARE...15   
   >100...OS1NC...100   
   >110...NCWARE...20   
   >110...PAWARE...30   
   >   
   >Output would be:   
   >Number...Desc1...NCWARE...PAWARE...OS1NC   
   >100...WidgetA...10...15...100   
   >110...WidgetB...20...30...0   
   >   
   >Is this possible? Thanks   
   >Shane   
      
   Hi Shane,   
      
   Here is one way:   
      
   SELECT     s.Number, s.Desc1,   
              SUM(CASE WHEN b.Warehouse = 'NCWARE' THEN b.Units ELSE 0   
   END) AS NCWARE,   
              SUM(CASE WHEN b.Warehouse = 'PAWARE' THEN b.Units ELSE 0   
   END) AS PAWARE,   
              SUM(CASE WHEN b.Warehouse = 'OS1NC' THEN b.Units ELSE 0   
   END) AS OS1NC   
   FROM       Stock AS s   
   INNER JOIN Bin   AS b   
         ON   b.Number = s.Number   
   GROUP BY   s.Number, s.Desc1;   
      
   This techique will only work if the number and names of the columns is   
   known in advance. If that is not the case, you can either google for   
   "dynamic pivot", or simply return the data to the client and do the   
   formatting there. My recommendation is the latter.   
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)