October 27, 2011 at 7:55 am
Please help me out below
I need to pullout the data from different tables
Table1
ID ID1 state
1 AA CA
2 BB TX
3 CC VA
4 DD MD
5 EE MA
Table2
ID1 Name State Address
AA AggAcc VA 1st
AA AeeAcc CA 2nd
BB BeeBcc TX 3rd
BB BggBee KY 4th
CC CaaCbb VA 5th
DD DaaDbb MD 6th
EE EaaEbb CA 7th
I have to pullout the data by using thes condition below
1)If ID1 in the second table assigned to the two Name then match with the state for example AA has two name in the second table so we have to match with their states and pullout the data like
1 AA AeeAcc CA 2nd
2) If ID1 is assigned to the only one name then then take the name infomation, even if state does't matches, For example state in the first table for EE and state in the second table of EE does't matches
Output looklike
5 EE EaaEbb MA 7th
Final
output look like
ID ID1 Name State Adress
1 AA AeeAcc CA 2nd
2 BB BeeBcc TX 3rd
3 CC CaaCbb VA 5th
4 DD DaaDbb MD 6th
5 EE EaaEbb MA 7th
Thanks
October 27, 2011 at 11:10 am
This isn't really an SSIS question. But here's a solution for you anyway:
CREATE TABLE #TestTable1
(
ID INT,
ID1 VARCHAR(10),
State CHAR(2)
)
CREATE TABLE #TestTable2
(
ID1 VARCHAR(10),
[Name] VARCHAR(20),
State CHAR(2),
Address VARCHAR(20)
)
INSERT INTO #TestTable1 (ID, ID1, State)
VALUES (1, 'AA', 'CA')
INSERT INTO #TestTable1 (ID, ID1, State)
VALUES (2, 'BB', 'TX')
INSERT INTO #TestTable1 (ID, ID1, State)
VALUES (3, 'CC', 'VA')
INSERT INTO #TestTable1 (ID, ID1, State)
VALUES (4, 'DD', 'MD')
INSERT INTO #TestTable1 (ID, ID1, State)
VALUES (5, 'EE', 'MA')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('AA', 'AggAcc', 'VA', '1st')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('AA', 'AeeAcc', 'CA', '2st')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('BB', 'BeeBcc', 'TX', '3rd')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('BB', 'BggBee', 'KY', '4th')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('CC', 'CaaCbb', 'VA', '5th')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('DD', 'DaaDbb', 'MD', '6th')
INSERT INTO #TestTable2 (ID1, [Name], State, Address)
VALUES ('EE', 'EaaEbb', 'CA', '7th')
CREATE TABLE #FinalRecords
(
ID INT,
ID1 VARCHAR(10),
State CHAR(2),
[Name] VARCHAR(20),
Address VARCHAR(20)
)
;
WITH cte AS
(
SELECT
ID,
#TestTable1.ID1,
#TestTable1.State,
#TestTable2.Name,
(CASE WHEN #TestTable1.State = #TestTable2.State THEN #TestTable2.Address ELSE NULL END) AS NullAddress
FROM #TestTable1
JOIN #TestTable2 ON #TestTable1.ID1 = #TestTable2.ID1
)
INSERT INTO #FinalRecords (ID, ID1, State, [Name], Address)
SELECT
ID,
ID1,
State,
[Name],
NullAddress
FROM cte
WHERE NullAddress IS NOT NULL
INSERT INTO #FinalRecords (ID, ID1, State, [Name], Address)
SELECT
ID,
#TestTable1.ID1,
#TestTable1.State,
#TestTable2.Name,
#TestTable2.Address
FROM #TestTable1
JOIN #TestTable2 ON #TestTable1.ID1 = #TestTable2.ID1
WHERE NOT EXISTS (SELECT 1 FROM #FinalRecords WHERE #FinalRecords.ID = #TestTable1.ID)
SELECT * FROM #FinalRecords
DROP TABLE #TestTable1
DROP TABLE #TestTable2
DROP TABLE #FinalRecords
October 27, 2011 at 1:51 pm
Thanks Kramaswamy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply