Combine related row from 2 tables into single row

  • Hi,

    I have two tables Projects and FundingSources

    How can i create a summary with each project per row with funding sources added?

    Projects:

    ProjectID, Name, Status, StartDt, EndDt

    R101, "Rail project", "NotStarted", "08/21/13", "08/21/14"

    A201, "Aviation project", "Started", "06/01/13", "06/30/14"

    B301, "BikeTransit project","Started"03/01/13", "03/30/14""

    FundingSources:

    ID, ProjectID, Agency, Amount, Approved

    1, R101, XYZ, $30,000, Y

    2, R101, ABC, $50,000, N

    3, A201, LML, $100,000, N

    Result:

    ProjectID, Name , Status ,Agency1 ,Amount1 ,Agency2 , Amount2,

    R101 ,"Rail project" , NotStarted ,XYZ ,$30,000 ,ABC ,$50,000

    A201 ,"Aviation project" , Started ,LML ,$100,000 , ,

    B301 ,"BikeTransit " ,Started , , , ,

    --Create Table Projects

    CREATE TABLE [dbo].[PROJECTS](

    [id] [int] NOT NULL,

    [projectid] [nvarchar](50) NOT NULL,

    [name] [nvarchar](50) NOT NULL,

    [status] [nvarchar](50) NOT NULL,

    [startdt] [datetime2](7) NOT NULL,

    [enddt] [datetime2](7) NULL,

    CONSTRAINT [PK_PROJECTS] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    -- create ProjectFunding table

    CREATE TABLE [dbo].[ProjectFunding](

    [id] [int] NOT NULL,

    [projectid] [nvarchar](50) NOT NULL,

    [agency] [nvarchar](50) NOT NULL,

    [amount] [nvarchar](25) NOT NULL,

    [approved] [nvarchar](5) NOT NULL,

    CONSTRAINT [PK_ProjectFunding] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    --insert data projects

    insert into Projects values(1, 'R101', 'Rail project', 'NotStarted', '08/21/13', '08/21/14');

    insert into Projects values(2, 'A201', 'Aviation project', 'Started', '06/01/13', '06/30/14');

    insert into Projects values(3, 'B301', 'BikeTransit project','Started', '03/01/13', '03/30/14');

    --insert data project funding

    insert into ProjectFunding values(1,'R101', 'XYZ', '$30,000', 'Y');

    insert into ProjectFunding values(2,'R101', 'ABC', '$50,000', 'N');

    insert into ProjectFunding values(3,'A201', 'LML', '$100,000', 'N');

    Appreciate any input.

  • Try Pivot Query

  • What do you want to do if there are more than 2 funding sources?

    Also, since you're new, have a look at the first link in my signature line below. You're almost there with what you posted. If you take it just one level higher, people will usually be a lot more helpful when it comes to code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ROUGH and lengthy query if you have more records ..

    using some other method i think you can make it short

    SELECT A.P,A.AGENCY1,A.AMOUNT1,B.AGENCY2,B.AMOUNT2 FROM

    (select PROJECTID AS P, AGENCY AS AGENCY1,AMOUNT AS AMOUNT1

    FROM [FundingSources]

    WHERE AGENCY='XYZ') A

    JOIN

    (select PROJECTID AS P,AGENCY AS AGENCY2,AMOUNT AS AMOUNT2

    FROM [FundingSources]

    WHERE AGENCY='ABC')B ON(A.P=B.P)

  • create a select statement for the funding table using a rownumber field so each project has a number based on the Project ID.

    Then do a straight select statement joining the 2 tables with a left outer join based on the ID field and in the select add a case statement that corresponds to all the possible numbers within the rownumber.

    Jason.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply