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 143

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 143

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity mvc , views, hidden values ? 2 22
point in time restore in SQL server 26 41
SQL server client app 3 25
Calculating percentage 2 26
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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 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