Combine related row from 2 tables into single row

Question

Post reply

Combine related row from 2 tables into single row

madhavsinghk

SSC Veteran

Points: 292
More actions
August 21, 2013 at 3:34 pm

#278926

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.

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

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

Snigdha Vartak Default port Points: 1486 More actions · Answer 1

Snigdha Vartak

Default port

Points: 1486

August 21, 2013 at 5:03 pm

#1643703

Try Pivot Query

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

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)

super48 SSCrazy Points: 2682 More actions · Answer 3

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)

RandomEvent SSCrazy Points: 2963 More actions · Answer 4

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.