How to acheive result set basing on cte's and levels

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi Guys,

Today I faced a strange question in interview.I can't even have any idea of it how to do

Question is

I have a student table.

SQL

Create table #student(sname varchar(20),medium varchar(20),class varchar(20))

insert into #student
values
('std1','Hindi','class-1'),
('std2','Hindi','class-1'),
('std3','Hindi','class-1'),
('std4','English','class-1'),
('std5','English','class-1'),
('std6','Hindi','class-2'),
('std7','Hindi','class-2'),
('std8','french','class-3'),
('std9','french','class-3'),
('std10','french','class-3'),
('std11','russian','class-3'),
('std12','russian','class-3')

this table has student name ,medium of study and class.

Now he wants resultset some thing like below format in a single column.

class
medium
students under this class and medium

like

DETAILS
class-1
English
std1   
std2   
std3   
class-1
Hindi  
std4   
std5   
class-2
Hindi  
std6   
std7   
class-3
french 
std8   
std9   
std10  
class-3
russian
std11  
std12

He gave me a clue that use CTE's and level concepts.

Anyhow I got rejected because of this.But I want to know the solution

Could any one help me out how to achieve this

Posted 31-Mar-15 4:26am

Member 11283674

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-03-31T04:40:00

This article[^] from Pinal Dave explains how to use Recursive CTEs to produce the Hierarchical results that you are after.

I came back to this to give a fuller explanation in light of your specific question as it can also be achieved using nested CTEs.

I decided that my anchor statement was

select distinct medium as detail, 2 as detlev, medium, class from #student

as I can easily get the class names and I know they will be level 1.
To get my recursion I added

SQL

UNION ALL

SELECT S.sname as detail, M1.detlev + 1, s.medium, S.class FROM #student S
INNER JOIN CTE1 M1 ON S.class = M1.Class and S.Medium = M1.detail

which gave me results of

English	2	English	class-1
french	2	french	class-3
Hindi	2	Hindi	class-1
Hindi	2	Hindi	class-2
russian	2	russian	class-3
std11	3	russian	class-3
std12	3	russian	class-3
std6	3	Hindi	class-2
std7	3	Hindi	class-2
std1	3	Hindi	class-1
std2	3	Hindi	class-1
std3	3	Hindi	class-1
std8	3	french	class-3
std9	3	french	class-3
std10	3	french	class-3
std4	3	English	class-1
std5	3	English	class-1

I then needed to UNION those results with the class names

select distinct class as detail, class, CAST(NULL as varchar(20)) as medium, 1 as detlev from #student

so my entire query became

SQL

;WITH CTE1 AS
(
    select distinct medium as detail, 2 as detlev, medium, class from #student

    UNION ALL

    SELECT S.sname as detail, M1.detlev + 1, s.medium, S.class FROM #student S
    INNER JOIN CTE1 M1 ON S.class = M1.Class and S.Medium = M1.detail
),
CTE2 AS
(
    SELECT DISTINCT detail, class, medium, detlev FROM CTE1 c1
    UNION ALL
    select distinct class as detail, class, CAST(NULL as varchar(20)) as medium, 1 as detlev from #student
)
SELECT detail FROM CTE2
order by class, medium, detlev,detail

giving me my final results of

class-1
English
std4
std5
Hindi
std1
std2
std3
class-2
Hindi
std6
std7
class-3
french
std10
std8
std9
russian
std11
std12

Note these results are different to those in your original question - that threw me for a moment until I determined that the expected results you quoted are not quite right given the data supplied.