Help on query

Question

Post reply

Help on query

Viewing 8 posts - 16 through 22 (of 22 total)

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

Longlongagoxz Mr or Mrs. 500 Points: 515 More actions · Answer 1

jethrow (8/19/2013)
Can you provide more info on input & desired output?

Hi jethrow,

are you talking with me?

I just ask vignesh, if mark1,mark2 and mark3 have same values, what result he wants to get?

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

@jeff Moden

Case is doing good ..

but that question was not for the case solution. that's for Erland's Query...

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 3

Hi jethrow ..

if the all the marks are same need to show all the column names

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

vignesh.ms (8/19/2013)
Hi jethrow ..
if the all the marks are same need to show all the column names

Here's a CROSS APPLY VALUES version. CAV is a longhand UNPIVOT, it's a little faster - but not as fast as CASE can be.

SELECT Name, x.MarkNames

FROM #sample

CROSS APPLY (

SELECT MarkNames = STUFF(

(SELECT ',' + d.MarkName

FROM (

SELECT cav.MarkName, r = DENSE_RANK() OVER(ORDER BY BestMark DESC)

FROM (VALUES (Mark1,'Mark1'),(Mark2,'Mark2'),(Mark3,'Mark3')) cav (BestMark, MarkName)

) d

WHERE r = 1

ORDER BY d.MarkName

FOR XML PATH(''))

,1,1,'')) x

ORDER BY Name;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

vignesh.ms (8/19/2013)
@Jeff Moden
Case is doing good ..
but that question was not for the case solution. that's for Erland's Query...

Understood. I just wanted to make sure that you weren't overlooking the possibility of the CASE solution. As you've stated, though, it won't show ties. Something like what Erland or Chris have done should do it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 6

;with cte as(

select name, Markname,Marks from(

select name,Mark1 ,

mark2 ,

mark3

from sample

) p unpivot

(Marks for Markname in (Mark1,Mark2,Mark3))

as unpvt

)

select name,Markname from cte

where exists (

select 1 from cte a

where a.name=cte.name

group by name

having Max(a.Marks)=cte.Marks)

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 7

;with cte as (select name, Markname,Marks,RANK() over (Partition by name order by Marks desc) as rm from(

select name,Mark1 ,

mark2 ,

mark3

from sample

) p unpivot

(Marks for Markname in (Mark1,Mark2,Mark3))

as unpvt)

select name,Markname from cte where rm=1

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 8

vignesh.ms

Hall of Fame

Points: 3456

August 19, 2013 at 7:11 am

#1642671

THANKS EVERY BODY