I HAVE A QUERY THIS IS PRODUCING THIS RESULT. EVERYTHING WORKS FINE BUT WHAT I WANT TO ACHIEVE IS SLIGHTLY DIFFERENT. INSTEAD OF HAVING REPEATING IMPORTERS, I WANT TO GROUP THE IMPORTERS/DISTRIBUTOR SO THAT NOTHING CAN REPEAT AND HAVE ALL THE BALANCES IN THREE SEPARATE COLUMNS. BELOW IS MY TABLE STRUCTURE:

TABLE 1 (ImporterDistributor) -->

ID        NAME 

TABLE 2 (PD_Temp_Balance)

DISTRIBUTOR    IMPORTER    BALANCE

BELOW IS MY QUERY.

   Select concat(ImporterDistributor.name, '/', ID.name) as [Distributor_Importer], PD_Temp_Balance.Balance
   from ImporterDistributor     
  join PD_Temp_Balance on (PD_Temp_Balance.Distributor=ImporterDistributor.ID )
  join ImporterDistributor as ID on (PD_Temp_Balance.Importer=ID.ID )
 where PD_Temp_Balance.Transaction_Date = '2014-11-01' 
 group by concat(ImporterDistributor.name, '/', ID.name), PD_Temp_Balance.Balance, PD_Temp_Balance.Product

AS IT STANDS, THIS IS MY RESULT:

DISTRIBUTOR/IMPORTER        BALANCE
Aminata/Aminata             0       
Aminata/Aminata             0   
Aminata/Aminata             19500   
Gepco/West Oil              11400   
Gepco/West Oil              11802   
Gepco/West Oil              20500   
Juice/MOTC                  0       
Juice/MOTC                  0       
Juice/MOTC                  1000    
Kailondo/Kailondo           0       
Kailondo/Kailondo           17500   
Kailondo/Kailondo           57500   
MOTC/MOTC                   0       
MOTC/MOTC                   500     
MOTC/MOTC                   39000   

WHAT I WANT TO ACHIEVE IS THIS:

DISTRIBUTOR/IMPORTER       BALANCEPMS    BALANCEAGO    BALANCEATK
Aminata/Aminata             0              0           19500    
Gepco/West Oil              11400          11802       20500    
Juice/MOTC                  0              0           1000 
Kailondo/Kailondo           0              17500       57500    
MOTC/MOTC                   0              500         39000    

If you provide me with Create Scripts for the Schema and some sample data I will have a go at it.

Create statement for table 1

CREATE TABLE [dbo].[ImporterDistributor](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NULL,
    [Contact_Person_FN] [nvarchar](50) NOT NULL,
    [Contact_Person_LN] [nvarchar](50) NOT NULL,
    [Address] [text] NOT NULL,
    [Contact_Number] [nvarchar](20) NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
    [Date_Entered] [datetime] NOT NULL,
 CONSTRAINT [PK_Importer] PRIMARY KEY CLUSTERED 
(

Create Script for Table 2

CREATE TABLE [dbo].[PD_Temp_Balance](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Importer] [int] NULL,
    [Distributor] [int] NULL,
    [Product] [int] NULL,
    [Balance] [int] NULL,
    [Transaction_Date] [date] NULL,
 CONSTRAINT [PK_PD_Temp_Balance] PRIMARY KEY CLUSTERED 
(

Table 1 is just a listing of Importer/Distributor with ID and name.

This article has been dead for over six months. Start a new discussion instead.