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

Get values from 2nd Table Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 5:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:49 AM
Points: 34, Visits: 132
Can someone show me how to combine these two tables and get one result set

CREATE TABLE_1
(
Field1 VARCHAR(4) NULL, Field2 VARCHAR(4) NULL, Field3 VARCHAR(4) NULL
)
INSERT INTO TABLE_1
VALUES(Field1, Field2, Field3)
(A, B, NULL),
(NULL, A, NULL),
(A, B, C),
(NULL, NULL, NULL),

CREATE TABLE_2
(
FieldName VARCHAR(4) NOT NULL, Title VARCHAR(20) NOT NULL)

INSERT INTO TABLE_2
(FieldName, Title)
Values (A, xyz),
(D, yyy)
(B, zzz)

Fieldname column data in TABLE_2 are matching in random column(Field1, Field2, and Field3) in TABLE_1

RESULT should shows Title of A or B etc... like
Field1, Field2, Field3
Title, Title, Title




Post #1476836
Posted Tuesday, July 23, 2013 6:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 11:20 AM
Points: 45, Visits: 139
Dear,
Use Union all operator to unified the result set.

Go to : http://www.w3schools.com/sql/sql_union.asp

Note. Datatype and no of columns should be same when you use union / union all,

Cheers
Post #1476840
Posted Tuesday, July 23, 2013 6:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 3,417, Visits: 5,328
Let's start by correcting your DDL and sample data so that it will run in MS SQL (converted to temp tables to reduce clutter in my sandbox):

CREATE TABLE #TABLE_1 
(
Field1 VARCHAR(4) NULL, Field2 VARCHAR(4) NULL, Field3 VARCHAR(4) NULL
);

INSERT INTO #TABLE_1 (Field1, Field2, Field3)
VALUES ('A', 'B', NULL),(NULL, 'A', NULL),('A', 'B', 'C'),(NULL, NULL, NULL);

CREATE TABLE #TABLE_2
(
FieldName VARCHAR(4) NOT NULL, Title VARCHAR(20) NOT NULL);

INSERT INTO #TABLE_2 (FieldName, Title)
Values ('A', 'xyz'),('D', 'yyy'),('B','zzz');


Next we'll apply the CROSS APPLY VALUES approach to UNPIVOT from #TABLE_1, eliminating NULLs and then doing a JOIN to #TABLE_2.

SELECT b.Field, c.Title
FROM #TABLE_1 a
CROSS APPLY (
VALUES (Field1),(Field2),(Field3)) b (Field)
INNER JOIN #Table_2 c ON b.Field = c.FieldName
WHERE Field IS NOT NULL;

Go
DROP TABLE #Table_1;
DROP TABLE #Table_2;


The CAV approach to UNPIVOT is explained in the first of my signature links below.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1476843
Posted Wednesday, July 24, 2013 4:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
You have an answer to the original question but I would like to point out that it smacks of poor table design. Why do you have values in a child table that are related to more than one column in the parent table. What is the real world application of this structure, we may be able to suggest a more suitable database layout that avoids the pain and confusion of CROSS APPLY or UNPIVOT

Post #1476976
Posted Monday, July 29, 2013 7:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:49 AM
Points: 34, Visits: 132
These table are not from production database, this is how table are created to hold data, so I am trying to clean and combine data from both tables.
Post #1478795
Posted Monday, July 29, 2013 8:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:49 AM
Points: 34, Visits: 132
I was able to get the result based on your answer, but first I had to use OUTER APPLY for FIELD1 and then CROSS APPLY on every other fields (FIELD2, FIELD3, FIELD4 and FIELD5). If I don't use OUTER APPLY then I don't get result for some reason. It was very helpful, but I still need to understand myself.
Post #1478796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse