November 2, 2016 at 8:14 am
Please perform below steps
We have different columns Rootlevel,level1,level2,level3 to Level6 .
Now Based on Each and every level(parent ,child )levels are there .Require a query all the distinct level values.
Note :Occurrence is present in all the sub levels .Want to know all the Total distinct levels in the orgstructure .
Rootlevel,level1,level2,level3,Level4,Level5
A A A B C A
A A A B C A
A B B C A B
November 2, 2016 at 8:42 am
Hate to say it without looking at the spreadsheet, but your wording suggests this is some kind of homework or test question. We don't do homework here, and given a 3 and a half MEGABYTE spreadsheet, I'll prefer not to take the chance that the spreadsheet might contain malware... We don't generally need megabytes of data to crunch on. How about you take the first 20 or 30 rows and publish those as INSERT statements to the temp table, and we can generalize from there, assuming we don't think we're doing your work for you....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 2, 2016 at 9:44 am
Sorry dude modified ...
November 2, 2016 at 10:05 am
mytesting9 (11/2/2016)
Please perform below stepsWe have different columns Rootlevel,level1,level2,level3 to Level6 .
Now Based on Each and every level(parent ,child )levels are there .Require a query all the distinct level values.
Note :Occurrence is present in all the sub levels .Want to know all the Total distinct levels in the orgstructure .
Rootlevel,level1,level2,level3,Level4,Level5
A A A B C A
A A A B C A
A B B C A B
Okay, now we need to translate what you mean by this into something everyone is going to understand. When you say "Based on Each and every level (parent ,child )levels are there.", I'm not at all sure what you're really trying to say, so how about we translate the representation you provided with the letters A, B, and C, into an actual INSERT script as I originally suggested. If I just take what you supplied, here's what my interpretation would be:
CREATE TABLE #TEST_DATA (
Rootlevel char(1),
level1 char(1),
level2 char(1),
level3 char(1),
Level4 char(1),
Level5 char(1),
Level6 char(1)-- I added this because of your description stating that this was how many levels there were.
);
INSERT INTO #TEST_DATA (Rootlevel, level1, level2, level3, Level4, Level5)-- Left off Level 6 as we don't have data for that.
VALUES('A', 'A', 'A', 'B', 'C', 'A'),
('A', 'A', 'A', 'B', 'C', 'A'),
('A', 'B', 'B', 'C', 'A', 'B');
-- THIS QUERY JUST SELECTS THE DATA - HOW ABOUT YOU SUPPLY WHAT QUERY OR QUERIES YOU'VE TRIED SO FAR?
-- ALTERNATIVELY, MAYBE THE STRUCTURE OF THE DATA NEEDS TO CHANGE ? ALSO, GIVEN THIS SET OF DATA,
-- WHAT WOULD BE THE EXPECTED RESULTS ? ALSO, CAN YOU FILL IN THE Level6 DATA ?
SELECT *
FROM #TEST_DATA;
-- JUST HERE TO REMOVE THE TEMP TABLE SO I DON'T LEAVE IT HANGING AROUND
DROP TABLE #TEST_DATA;
We're going to need some feedback on:
1.) What have you tried so far?
2.) Is the data properly represented in the SQL shown above?
3.) What would be the expected result? (actual data result, not just a description)
Let us know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply