BREAK ON (PL/SQL Oracle) Equivanlent in SQL Server

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 1

can you give us more information what does "break on" do ?

SQLQuest SSC-Addicted Points: 445 More actions · Answer 2

Hello Marcin,

I have attached a document that explains my example.

Thanks,

Shashi

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 3

there is no substition for break on ;/ but maybe you can use something like this ?

declare @t as table (name varchar(50), dep varchar(10), num int)

insert into @t values ('Marcin','IT',1),('John','IT',2),('Marcin','IT',3),('John','IT',4),('Marcin','IT',5),('Steve','IT',1),

('Marcin','SEC',1),('John','MAR',2),('Marcin','SEC',3),('John','FIN',4),('Marcin','MAR',5),('Steve','FIN',1)

;with cte as(

select

dense_rank() over (order by name) as id1

,row_number() over (partition by name order by dep) as id2

, name as intname,dep,SUM(num) as summary from @t

group by name,dep

)

select name = case id2

when 1 then intname

else ''

end, dep, summary from cte

order by intname,dep

SQLQuest SSC-Addicted Points: 445 More actions · Answer 4

This did help. Thank you ....

I have come across a good question. I am attaching a word file describing the problem.

I have tried solving the problem but I did not get the exact formatting as required.

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 5

declare @table as table (student varchar(50), course varchar(50), marks int)

insert into @table values ('Sam', 'Maths',10),('Sam', 'Science',10),('Sam', 'English',30),

('John', 'Maths',60),('John', 'Science',70),('John', 'English',90)

select student, Maths, Science, English

from

(select marks, student, course from @table) t1

pivot

(

min(marks)

for Course in

(Maths, Science, English)

) as t2

order by t2.student

SQLQuest SSC-Addicted Points: 445 More actions · Answer 6

It got me the output in the desired format ....

Can you please explain me the concept of "pivot" and the logic behind this query ......

I am just aware of the SELF JOIN

Thanks,

Shashi