Solved

SQL command

Posted on 2011-02-14
3
278 Views
Last Modified: 2012-05-11
I have  the folloiwng table structure

partstable
partno (PK) partname
P101           part1

stocktable
partno (PK) storeID(PK) <---Combined PK stock
P101             s1                        100
p101             s2                         50

How can I retrive all rows from store stocktable and related records from partstable? I want the result as follows

PartNo  partname   s1   s2
P101       part1   100   50

pls. help

ayha
0
Comment
Question by:ayha1999
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34889580
this seems like a pivot statement ... unless you know the s1 and s2 dynamically, in which case it becomes a dynamic pivot
let's see if this helps:
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
0
 
LVL 7

Author Comment

by:ayha1999
ID: 34889970
I am not able to follow it. could u pls help me.

thanks

ayha
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34890037
let's see with a non-dynamic version
SELECT PartNo  
     , PartName
     , s1
     , s2
  FROM ( SELECT p.partno, p.partname, s.stockno, sum(s.saleamount) sales_amount
           FROM partstable p
           LEFT JOIN SalesTable s
             ON p.partno = s.partno 
          GROUP BY p.partno, p.partname, s.stockno
        ) p
    PIVOT ( SUM(sales_amount)
             FOR StockNo IN ([s1],[s2] )
           ) AS pvt

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now