November 21, 2016 at 7:48 am
I need to sort through Table1 and look through each distinct MAIN_ID. In looking at each MAIN_ID I need to look through the MAIN_ID's SUB_ID and determine which DESC is missing from each SUB_ID of the MASTER_ID. Each distinct MASTER_ID's SUB_ID needs to have the same number of description categories as each SUB_ID. For those rows being added I need my 4th column to be valued with a 0
For example I have Table1
Table1 sample data...
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 XYZ TEXT 30
1234 XYZ TEXT5 76
I need my final table to look like this...
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 ABC TEXT5 0
1234 XYZ TEXT 30
1234 XYZ TEXT2 0
1234 XYZ TEXT5 76
Thank you for any help that someone may provide me.
November 21, 2016 at 9:47 am
Short answer is that it's a cross join of the first 3 columns (all possible combinations) and then outer join back to the original table to pick up the nulls and convert them to zeroes.
November 21, 2016 at 10:18 am
Here's an example of how you could tackle this:
DECLARE @sample TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100));
INSERT @sample VALUES
('1234 ABC', 'TEXT', '80'),
('1234 ABC', 'TEXT2', '20'),
('1234 XYZ', 'TEXT', '30'),
('1234 XYZ', 'TEXT5', '76');
WITH
c1 AS (SELECT col1 FROM @sample GROUP BY col1),
c2 AS (SELECT col2 FROM @sample GROUP BY col2),
x AS (SELECT * FROM c1, c2)
SELECT x.col1, x.col2, col3 = ISNULL(s.col3,0)
FROM x
LEFT JOIN @sample s ON x.col1 = s.col1 AND x.col2 = s.col2
ORDER BY col1, col2; -- not required, including for demonstration purposes.
Results:
col1 col2 col3
--------- ------ -----
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 ABC TEXT5 0
1234 XYZ TEXT 30
1234 XYZ TEXT2 0
1234 XYZ TEXT5 76
-- Itzik Ben-Gan 2001
November 21, 2016 at 11:27 am
Hi Alan,
Thank you for your response. There are 4 columns actually the 1234 is one column, ABC is another, then TEXT is another, and the PCT column is the last 4th column.
Is the code still the same but adding one more column?
Thank you!
November 21, 2016 at 11:37 am
Got it. Note the mild tweak:
DECLARE @sample TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 int);
INSERT @sample VALUES
('1234', 'ABC', 'TEXT', '80'),
('1234', 'ABC', 'TEXT2', '20'),
('1234', 'XYZ', 'TEXT', '30'),
('1234', 'XYZ', 'TEXT5', '76');
WITH
c1 AS (SELECT col1, col2 FROM @sample GROUP BY col1, col2),
c2 AS (SELECT col3 FROM @sample GROUP BY col3),
x AS (SELECT * FROM c1, c2)
SELECT x.col1, x.col2, x.col3, col4 = ISNULL(s.col4,0)
FROM x
LEFT JOIN @sample s ON x.col1 = s.col1 AND x.col2 = s.col2 AND x.col3 = s.col3
ORDER BY col1, col2; -- not required, including for demonstration purposes.
-- Itzik Ben-Gan 2001
November 21, 2016 at 11:58 am
one issue I am seeing is that there are descriptions from other MASTER_ID's. each MASTER_ID has specific desc which should not be appearing in other MASTER_ID's
for example MASTER_ID '9876' descriptions should not appear in MASTER_ID's 1234.... Hopefully this makes sense. THANKS!!
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 ABC TEXT5 0
1234 XYZ TEXT 30
1234 XYZ TEXT2 0
1234 XYZ TEXT5 76
9876 EFG TEXT9 80
9876 EFG TEXT8 90
9876 LMN TEXT 8 20
9876 LMN TEXT 9 80
November 21, 2016 at 12:34 pm
Are there tables with the data in it somewhere that you could share? From your sample data, it's not entirely clear which columns belong in which logical group (table).
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 ABC TEXT5 0
1234 XYZ TEXT 30
1234 XYZ TEXT2 0
1234 XYZ TEXT5 76
9876 EFG TEXT9 80
9876 EFG TEXT8 90
9876 LMN TEXT 8 20
9876 LMN TEXT 9 80
It looks like the first two columns make up one table,... where do the second two columns come from? What do they mean?
November 21, 2016 at 1:19 pm
This is one table with 4 columns. COL1 = MASTER_ID, COL2 = SUB_ID, COL3 = DESC, COL4 = %
Each MASTER_ID has SUB_ID's... for each SUB_ID in the set of a MASTER_ID such as 1234 each SUB_ID needs to have the same descriptions. for example if MASTER_ID 1234 has SUB_ID ABC and XYZ they both need to contain the same descriptions, any descriptions being added need to match the SUB_ID's within that MASTER_ID need to have 0 populated in the PCT column.
Think about it this way... each MASTER_ID is its own separate entity.
Does this help?
1234 ABC TEXT 80
1234 ABC TEXT2 20
1234 ABC TEXT5 0
1234 XYZ TEXT 30
1234 XYZ TEXT2 0
1234 XYZ TEXT5 76
9876 EFG TEXT9 80
9876 EFG TEXT8 90
9876 LMN TEXT 8 20
9876 LMN TEXT 9 80
November 21, 2016 at 1:45 pm
I also have a table with the distinct values for the DESC column for every MASTER_ID which is structured as follow....
Not sure if you could use this table to get the result I wish to get
COL1 COL2
1234 TEXT
1234 TEXT1
1234 TEST3
9876 TEXT9
9876 TEXT8
November 21, 2016 at 2:56 pm
Can you please explain the logic of how to get the result you're looking for? Maybe I'm slow, but that part baffles me. Maybe there's something you understand about the data that I don't. Do these things represent something real?
November 21, 2016 at 3:31 pm
let me try it this way HAHA.
Table 1 has 4 columns and the mock data looks like this...
1234 ABC TEXT 80
1234 XYZ TEXT2 20
1234 XYZ TEXT4 30
9876 XYZ TEXT2 20
9876 XYZ TEXT5 10
9876 ABC TEXT1 20
Table 2 contains the
Distinct values for each MASTER_ID's DESC which is column 3 of table 1 ....
1234 TEXT
1234 TEXT2
1234 TEXT4
9876 TEXT2
9876 TEXT5
9876 TEXT1
I need to look through TABLE1 and find which DESC are missing for each SUB_ID for each specific MASTER_ID. Each MASTER_ID has nothing to do with the other... which means that I should not see descriptions that do not exist in the MASTER_PLAN. For example MASTER_ID 9876 has a DESC of TEXT5. This DESC should only appear in MASTER_ID's 9876 DESC's. The SUB_ID can exist in different MASTER_ID. But as stated before each MASTER_ID should be thought of as stand alone.
the final table should look like this....
1234 ABC TEXT 80
1234 ABC TEXT2 0
1234 ABC TEXT4 0
1234 XYZ TEXT2 20
1234 XYZ TEXT4 30
1234 XYZ TEXT 0
9876 XYZ TEXT2 20
9876 XYZ TEXT5 10
9876 XYZ TEXT1 0
9876 ABC TEXT1 20
9876 ABC TEXT2 0
9876 ABC TEXT5 0
Hope this helps 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply