Solved

SQL command

Posted on 2011-02-14
3
289 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

25 Experts available now in Live!

Get 1:1 Help Now