Joining Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining Tables

Author

Topic

Laylow
Starting Member

14 Posts

Posted - 2011-06-16 : 09:05:13

Hi All,

I have 2 tables which I want to join information from Table 2's rows where a condition exist(i.e.

 WHERE Code IN ('10','11','13')

with distinctive Codes) into Table 1.

I should then end up with the Desired Result shown below.

My code thus far is

 Select A.Client, A.Period, B.Code1, B.Code2, B.Code3
FROM Table 2 B 
INNER JOIN Table 1 A 
ON A.Client = B.Client 
WHERE Code IN ('10','11','13')

Is there a way I could achieve the "desired result" below using the following please?

Table 1
Client Period
CLI1 2011001
CLI2 2011001
CLI3 2011001

CLI is the Client code!
Table 2
Code Client Amount
10 CLI1 20
11 CLI1 30
13 CLI1 50

Desired Result
Code Period Code1 Code2 Code3
CLI1 2011001 20 30 50

Regards

Laylow

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-16 : 09:10:11

maybe pivot will do that...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-16 : 10:33:07

Here is one way:

-- Prepare sample data
DECLARE @Table1 TABLE  (Client CHAR(4), Period INT)
INSERT @Table1 (Client, Period)
VALUES
('CLI1', 2011001),
('CLI2', 2011001),
('CLI3', 2011001)


DECLARE @Table2 TABLE (Code INT, Client CHAR(4), Amount INT)
INSERT @Table2 (Code, Client, Amount)
VALUES
(10, 'CLI1', 20),
(11, 'CLI1', 30),
(13, 'CLI1', 50)

-- Return results
SELECT
    A.Client AS Code,
    A.Period,
    MAX(CASE WHEN B.Code = 10 THEN B.Amount ELSE NULL END) AS Code1,
    MAX(CASE WHEN B.Code = 11 THEN B.Amount ELSE NULL END) AS Code2,
    MAX(CASE WHEN B.Code = 13 THEN B.Amount ELSE NULL END) AS Code3
FROM
    @Table1 AS A
INNER JOIN
    @Table2 AS B
    ON A.Client = B.Client    
GROUP BY
    A.Client,
    A.Period

Laylow
Starting Member

14 Posts

Posted - 2011-06-16 : 12:32:18

Thanks a million Lamprey,

I will give this a try and get back to you soon.

Regards

Laylow

Laylow
Starting Member

14 Posts

Posted - 2011-06-16 : 20:49:10

Hi Lamprey,

I really appreciate you time and efforts.

I have a question regarding the 'VALUES' in the query.

The records in Table 1 and Table 2 are in excess of 20,000 rows.

Is there a way I can write this without declaring 'values' in the query.

Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-17 : 10:15:00

That is just sample data. You need to change the query to use your real table and column names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources