Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Combine related row from 2 tables into single row Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:56 PM
Points: 4, Visits: 28
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.
Post #1486980
Posted Wednesday, August 21, 2013 5:03 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 11:34 AM
Points: 522, Visits: 235
Try Pivot Query
Post #1486996
Posted Wednesday, August 21, 2013 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 35,262, Visits: 31,746
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1486997
Posted Thursday, August 22, 2013 4:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:41 AM
Points: 68, Visits: 412
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)
Post #1487163
Posted Thursday, August 22, 2013 6:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 519, Visits: 195
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.
Post #1487206
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse