Eliminate NULL values
-
Hi..I need the result from the below SQL code as:
IF OBJECT_ID('Tempdb..#tTable1') IS NOT NULLDROP TABLE #tTable1
CREATE TABLE #tTable1(ID INT,
Value1 VARCHAR(50),
Value2 VARCHAR(20))
INSERT INTO #tTable1(ID, Value1, Value2)
VALUES
(222,'A','B'),
(222,'R','F'),
(222,'T','C')
IF OBJECT_ID('Tempdb..#tTable2') IS NOT NULL
DROP TABLE #tTable2
CREATE TABLE #tTable2(ID INT,
Value3 VARCHAR(50),
Value4 DATETIME)
INSERT INTO #tTable2(ID, Value3, Value4)
VALUES
(222,'A',GETDATE()),
(222,'A',GETDATE()+3),
(222,'C',GETDATE()-8),
(222,'R',GETDATE()+2),
(222,'T',GETDATE()-5)
SELECT ID, Value1, Value2, Value3 = NULL, Value4 = NULL FROM #tTable1
UNION ALL
SELECT ID, Value1 = NULL, Value2 = NULL, Value3, Value4 FROM #tTable2
-
SELECT t1.Id,t.Value1,t.Value2,t1.Value3,t1.Value4 FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Id)AS Row,Id,Value3,CONVERT(VARCHAR,Value4,101) AS Value4 FROM #tTable2) AS t1
LEFT JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY Id)AS Row,Id,Value1,Value2 FROM #tTable1 )AS t
ON t.Row = t1.Row
-
Here is a quick solution, adds a row number to join the two sets
😎
IF OBJECT_ID('Tempdb..#tTable1') IS NOT NULLDROP TABLE #tTable1
CREATE TABLE #tTable1(ID INT,
Value1 VARCHAR(50),
Value2 VARCHAR(20))
INSERT INTO #tTable1(ID, Value1, Value2)
VALUES
(222,'A','B'),
(222,'R','F'),
(222,'T','C')
IF OBJECT_ID('Tempdb..#tTable2') IS NOT NULL
DROP TABLE #tTable2
CREATE TABLE #tTable2(ID INT,
Value3 VARCHAR(50),
Value4 DATETIME)
INSERT INTO #tTable2(ID, Value3, Value4)
VALUES
(222,'A',GETDATE()),
(222,'A',GETDATE()+3),
(222,'C',GETDATE()-8),
(222,'R',GETDATE()+2),
(222,'T',GETDATE()-5);
;WITH TBL_1 AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value1) AS VALUE_RID
,ID
,Value1
,Value2
FROM #tTable1
)
,TBL_2 AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value3) AS VALUE_RID
,ID
,Value3
,Value4
FROM #tTable2
)
SELECT
T2.ID
,T1.Value1
,T1.Value2
,T2.Value3
,T2.Value4
FROM TBL_2 T2
LEFT OUTER JOIN TBL_1 T1
ON T2.ID = T1.ID
AND T2.VALUE_RID = T1.VALUE_RID;
Results
ID Value1 Value2 Value3 Value4---- ------- ------- ------- -----------------------
222 A B A 2014-08-28 09:09:43.257
222 R F A 2014-08-31 09:09:43.257
222 T C C 2014-08-20 09:09:43.257
222 NULL NULL R 2014-08-30 09:09:43.257
222 NULL NULL T 2014-08-23 09:09:43.257
-
Thank you for the replies!.. But I`m in a situation where I have almost 12 temp tables.
For example for 4 tables data the result should be as below for the below 4 tables sql code:
IF OBJECT_ID('Tempdb..#tTable1') IS NOT NULLDROP TABLE #tTable1
CREATE TABLE #tTable1(ID INT,
Value1 VARCHAR(50),
Value2 VARCHAR(20))
INSERT INTO #tTable1(ID, Value1, Value2)
VALUES
(222,'A','B'),
(222,'R','F'),
(222,'T','C')
IF OBJECT_ID('Tempdb..#tTable2') IS NOT NULL
DROP TABLE #tTable2
CREATE TABLE #tTable2(ID INT,
Value3 VARCHAR(50),
Value4 DATETIME)
INSERT INTO #tTable2(ID, Value3, Value4)
VALUES
(222,'A',GETDATE()),
(222,'A',GETDATE()+3),
(222,'C',GETDATE()-8),
(222,'R',GETDATE()+2),
(222,'T',GETDATE()-5)
IF OBJECT_ID('Tempdb..#tTable3') IS NOT NULL
DROP TABLE #tTable3
CREATE TABLE #tTable3(ID INT,
Value5 VARCHAR(50))
INSERT INTO #tTable3(ID, Value5)
VALUES
(222,'AAA'),
(222,'BBB'),
(222,'TTT'),
(222,'DDD'),
(222,'NNN'),
(222,'OOO'),
(222,'JJJ'),
(222,'HHH'),
(222,'TTT'),
(222,'UUU'),
(222,'PPP'),
(222,'WWW')
IF OBJECT_ID('Tempdb..#tTable4') IS NOT NULL
DROP TABLE #tTable4
CREATE TABLE #tTable4(ID INT,
Value6 INT)
INSERT INTO #tTable4(ID, Value6)
VALUES
(222,1000),
(222,1001),
(222,1002),
(222,1003)
SELECT ID, Value1, Value2, Value3 = NULL, Value4 = NULL, Value5 = NULL, Value6 = NULL FROM #tTable1
UNION ALL
SELECT ID, Value1 = NULL, Value2 = NULL, Value3, Value4, Value5 = NULL, Value6 = NULL FROM #tTable2
UNION ALL
SELECT ID, Value1 = NULL, Value2 = NULL, Value3 = NULL, Value4 = NULL, Value5, Value6 = NULL FROM #tTable3
UNION ALL
SELECT ID, Value1 = NULL, Value2 = NULL, Value3 = NULL, Value4 = NULL, Value5= NULL,Value6 FROM #tTable4
DROP TABLE #tTable1
DROP TABLE #tTable2
DROP TABLE #tTable3
DROP TABLE #tTable4
-
So extrapolate on Eirikur's code for all the rest of the tables. If you don't understand the example he provided, we can walk you through it. However, there's nothing that connects your records, so you need to create one, thus the method of creating a RowID (VALUE_RID in his example) out of thin air, so you can control for repitition via JOIN.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
The connection between all the tables is the ID "222". This will be same in all the tables.
-
Thus causing duplication, which you're trying to avoid, so you need a way to have each line have a connection to all the other lines but in a unique way. That's the purpose of the VALUE_RID. You OUTER JOIN on both 222 and VALUE_RID.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
thank you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply