March 31, 2012 at 2:15 pm
Hi All,
I have encountered a problem. I believe that this community will solve the problem./I am a beginner/
The situation is the following.
say I have two tables, T1 and T2 ( T1 and T2 are Temp table drives from others using different parameters). Consider the following sample data.
Table T1 (PK1 C1 C2 C3 C4 C5 C6 C7) --- PK1 Unique in T1
Table T2 (PK2 Pk1 C1 C2 C3 C4 C5 C6 C7) -- PK2 is Unique in T2
Take the following data for table T1
Pk1 C1 C2 C3 C4 C5 C6 C7
1 5 6 10 5 10 NULL NULL
2 10 5 30 40 10 5 NULL
3 10 40 40 10 NULL NULL NULL
Take the following Data for Table T2
PK2 Pk1 C1 C2 C3 C4 C5 C6 C7
1 1 5 5 7 5 7 NULL NULL
2 1 4 2 5 4 10 NULL NULL
3 1 2 NULL 3 4 7 NULL NULL
4 1 3 5 6 NULL 9 NULL NULL
5 1 NULL 5 10 4 7 NULL NULL
6 1 5 6 NULL NULL 10 NULL NULL
7 2 5 4 25 35 3 5 NULL
8 2 4 5 NULL 4 9 4 NULL
9 3 10 40 5 7 NULL NULL NULL
10 3 10 NULL 5 8 NULL NULL NULL
PK1 in T1 and PK1 in T2 are the same and C1 ,C2 , C3,C4,C5,C6,C7 in T1 have similar column name with C1 ,C2 , C3,C4,C5,C6,C7 in T2 respectively.
Rows in T1 and T2 having the same Value of Pk1 ,if the value of any column in T1 is NULL it is also NULL in T2.
My problem is , If Pk1=1 is given I want to select rows in T2 where all columns having None NULL Vales except the column where its value is NULL in T1. That means the required is
PK2 Pk1 C1 C2 C3 C4 C5 C6 C7
1 1 5 5 7 5 7 NULL NULL
2 1 4 2 5 4 10 NULL NULL
If Pk1=2 is given I want to select rows in T2 where all columns having none NULL Vales except the column where its value is NULL in T1. That means the required is
PK2 Pk1 C1 C2 C3 C4 C5 C6 C7
7 2 5 4 25 35 3 5 NULL
If Pk1=3 is given I want to select rows in T2 where all columns having none NULL Vales except the column where its value is NULL in T1. That means the required is
PK2 Pk1 C1 C2 C3 C4 C5 C6 C7
9 3 10 40 5 7 NULL NULL NULL
Any Help?
March 31, 2012 at 2:59 pm
This?
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2
;
CREATE TABLE T1
(
PK1 INT
,C1 INT
,C2 INT
,C3 INT
,C4 INT
,C5 INT
,C6 INT
,C7 INT
);
CREATE TABLE T2
(
PK1 INT
,PK2 INT
,C1 INT
,C2 INT
,C3 INT
,C4 INT
,C5 INT
,C6 INT
,C7 INT
);
INSERT INTO T1 ( Pk1,C1,C2,C3,C4,C5,C6 ,C7 )
SELECT 1,5 ,6 ,10 ,5 , 10 , NULL , NULL
UNION ALL SELECT 2,10,5,30 ,40 ,10 , 5 , NULL
UNION ALL SELECT 3,10,40,40 ,10 ,NULL , NULL ,NULL
INSERT INTO T2 ( PK2,PK1,C1,C2,C3,C4,C5,C6 ,C7 )
SELECT 1, 1, 5, 5, 7, 5, 7 , NULL , NULL
UNION ALL SELECT 2, 1, 4, 2, 5, 4, 10 , NULL , NULL
UNION ALL SELECT 3, 1, 2, NULL , 3, 4, 7 , NULL , NULL
UNION ALL SELECT 4, 1, 3, 5, 6, NULL , 9 , NULL, NULL
UNION ALL SELECT 5, 1, NULL , 5, 10, 4, 7 , NULL, NULL
UNION ALL SELECT 6, 1, 5, 6, NULL , NULL , 10 , NULL, NULL
UNION ALL SELECT 7, 2, 5, 4, 25,35, 3 , 5, NULL
UNION ALL SELECT 8, 2, 4, 5, NULL , 4, 9 , 4, NULL
UNION ALL SELECT 9, 3, 10, 40, 5, 7, NULL , NULL, NULL
UNION ALL SELECT 10,3, 10, NULL,5, 8, NULL ,NULL , NULL
;
DECLARE @Pk_iD INT ;
SET @Pk_iD = 3 ;
DECLARE @SQL_Query VARCHAR(4000) = ''
,@ListOfNonNullCols VARCHAR(2000) = ''
;
SELECT @ListOfNonNullCols = STUFF (
( CASE WHEN T.C1 IS NULL THEN '' ELSE '+C1 ' END
+ CASE WHEN T.C2 IS NULL THEN '' ELSE '+C2 ' END
+ CASE WHEN T.C3 IS NULL THEN '' ELSE '+C3 ' END
+ CASE WHEN T.C4 IS NULL THEN '' ELSE '+C4 ' END
+ CASE WHEN T.C5 IS NULL THEN '' ELSE '+C5 ' END
+ CASE WHEN T.C6 IS NULL THEN '' ELSE '+C6 ' END
+ CASE WHEN T.C7 IS NULL THEN '' ELSE '+C7 ' END ) , 1, 1, '')
FROM T1 T
WHERE T.PK1 = @Pk_iD
;
IF @ListOfNonNullCols IS NOT NULL AND LEN(@ListOfNonNullCols) <> 0
BEGIN
SELECT @SQL_Query = 'SELECT * FROM T2 '
+ 'WHERE PK1 = ' + CONVERT(VARCHAR(19) , @Pk_iD) + ' '
+ 'AND (' + @ListOfNonNullCols + ') IS NOT NULL'
--SELECT @SQL_Query
EXEC (@SQL_Query)
END
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2
;
March 31, 2012 at 3:21 pm
This of course can be broken by CONCAT_NULL_YIELDS_NULL setting. So care should be taken before executing this script.
April 1, 2012 at 1:33 am
Really thank you very much! I am satisfied.
Now I want to to select columns from T2 where the corresponding column is NOT NULL in T1
for Example if Pk1=1 the expected result should exclude C6, and C7. The result should be
PK2 Pk1 C1 C2 C3 C4 C5
1 1 5 5 7 5 7
2 1 4 2 5 4 10
IF PK1=2 the result should be
PK2 Pk1 C1 C2 C3 C4 C5 C6
7 2 5 4 25 35 3 5
and If PK1=3 the result should be
PK2 Pk1 C1 C2 C3 C4
9 3 10 40 5 7
April 1, 2012 at 3:52 am
mollaa (4/1/2012)
Now I want to to select columns from T2 where the corresponding column is NOT NULL in T1
I was 200% sure that this request would come!!! :w00t:
Try this:
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2
;
CREATE TABLE T1
(
PK1 INT
,C1 INT
,C2 INT
,C3 INT
,C4 INT
,C5 INT
,C6 INT
,C7 INT
);
CREATE TABLE T2
(
PK1 INT
,PK2 INT
,C1 INT
,C2 INT
,C3 INT
,C4 INT
,C5 INT
,C6 INT
,C7 INT
);
INSERT INTO T1 ( Pk1,C1,C2,C3,C4,C5,C6 ,C7 )
SELECT 1,5 ,6 ,10 ,5 , 10 , NULL , NULL
UNION ALL SELECT 2,10,5,30 ,40 ,10 , 5 , NULL
UNION ALL SELECT 3,10,40,40 ,10 ,NULL , NULL ,NULL
;
INSERT INTO T2 ( PK2,PK1,C1,C2,C3,C4,C5,C6 ,C7 )
SELECT 1, 1, 5, 5, 7, 5, 7 , NULL , NULL
UNION ALL SELECT 2, 1, 4, 2, 5, 4, 10 , NULL , NULL
UNION ALL SELECT 3, 1, 2, NULL , 3, 4, 7 , NULL , NULL
UNION ALL SELECT 4, 1, 3, 5, 6, NULL , 9 , NULL, NULL
UNION ALL SELECT 5, 1, NULL , 5, 10, 4, 7 , NULL, NULL
UNION ALL SELECT 6, 1, 5, 6, NULL , NULL , 10 , NULL, NULL
UNION ALL SELECT 7, 2, 5, 4, 25,35, 3 , 5, NULL
UNION ALL SELECT 8, 2, 4, 5, NULL , 4, 9 , 4, NULL
UNION ALL SELECT 9, 3, 10, 40, 5, 7, NULL , NULL, NULL
UNION ALL SELECT 10,3, 10, NULL,5, 8, NULL ,NULL , NULL
;
DECLARE @Pk_iD INT ;
SET @Pk_iD = 2 ;
DECLARE @SQL_Query VARCHAR(4000) = ''
,@ListOfNonNullCols VARCHAR(2000) = ''
,@SelectList VARCHAR(2000) = ''
;
SELECT @ListOfNonNullCols = STUFF (
( CASE WHEN T.C1 IS NULL THEN '' ELSE '+C1 ' END
+ CASE WHEN T.C2 IS NULL THEN '' ELSE '+C2 ' END
+ CASE WHEN T.C3 IS NULL THEN '' ELSE '+C3 ' END
+ CASE WHEN T.C4 IS NULL THEN '' ELSE '+C4 ' END
+ CASE WHEN T.C5 IS NULL THEN '' ELSE '+C5 ' END
+ CASE WHEN T.C6 IS NULL THEN '' ELSE '+C6 ' END
+ CASE WHEN T.C7 IS NULL THEN '' ELSE '+C7 ' END ) , 1, 1, '')
FROM T1 T
WHERE T.PK1 = @Pk_iD
;
IF @ListOfNonNullCols IS NOT NULL AND LEN(@ListOfNonNullCols) <> 0
BEGIN
SELECT @SelectList = REPLACE (@ListOfNonNullCols,'+',',') ;
SELECT @SQL_Query = 'SELECT PK1, PK2 , '+@SelectList+' FROM T2 '
+ 'WHERE PK1 = ' + CONVERT(VARCHAR(19) , @Pk_iD) + ' '
+ 'AND (' + @ListOfNonNullCols + ') IS NOT NULL'
--SELECT @SQL_Query
EXEC (@SQL_Query)
END
;
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2
;
April 1, 2012 at 9:53 am
You are great! Thank you very much. My problem is solved! Please keep it up supporting others who are struggling to know more......:w00t:
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply