Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #777 > unrolled thread

One to many issue

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  One to many issue srgebauer@gmail.com - 2011-11-02 13:15 -0700
    Re: One to many issue Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-11-02 22:59 +0100
    Re: One to many issue srgebauer@gmail.com - 2011-11-03 13:31 -0700

#777 — One to many issue

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

[toc] | [next] | [standalone]

#778

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

[toc] | [prev] | [next] | [standalone]

#780

Worked perfectly thanks

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web