SQL command

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

partno (PK) partname
P101           part1

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

Question by:ayha1999
  • 2
LVL 142

Accepted Solution

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:

Author Comment

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


LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34890037
let's see with a non-dynamic version
     , 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


Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

785 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