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

On Wed, 2 Nov 2011 13:15:30 -0700 (PDT), srgebauer (AT) gmail (DOT) com wrote:

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

Worked perfectly thanks