October 23, 2013 at 1:15 am
Hi All,
This is my sample table please have a look on that
Create Table MySampleTable
(
Name NVarchar(25),
SubjectWiseMarks NVarchar(25)
)
Insert Into MySampleTable Values ('AAA','Maths-100')
Insert Into MySampleTable Values ('AAA','Physics-90')
Insert Into MySampleTable Values ('AAA','Chemistry-80')
Insert Into MySampleTable Values ('BBB','Maths-90')
Insert Into MySampleTable Values ('BBB','Physics-80')
Insert Into MySampleTable Values ('CCC','Maths-80')
CURRENT OUTPUT:
NameSubjectWiseMarks
AAAMaths-100
AAAPhysics-90
AAAChemistry-80
BBBMaths-90
BBBPhysics-80
CCCMaths-80
But My Required OUTPUT is Like This,
REQUIRED OUTPUT:
NameSubjectWiseMarks
AAA Maths-100
Physics-90
Chemistry-80
BBB Maths-90
Physics-80
CCC Maths-80
Please give me the solution.
Thanks in Advance,
VenkiDesai.
October 23, 2013 at 1:25 am
NameSubjectWiseMarks
AAAMaths-100
Physics-90
Chemistry-80
BBBMaths-90
Physics-80
CCCMaths-80
What Exactly u r looking for..???
Do u want to merge these two columns in One COLUMN in Select Statement, With grouping The Unique Name..??
October 23, 2013 at 1:33 am
Mr. Kapsicum (10/23/2013)
NameSubjectWiseMarks
AAAMaths-100
Physics-90
Chemistry-80
BBBMaths-90
Physics-80
CCCMaths-80
What Exactly u r looking for..???
Do u want to merge these two columns in One COLUMN in Select Statement, With grouping The Unique Name..??
See, if this Helps,
;with cte as
(
select ROW_NUMBER() over (partition by name order by name) as RN,* from #MySampleTable
)
select case when Rn=1 then Name+SubjectWiseMarks else SubjectWiseMarks End NewColumn from cte
October 23, 2013 at 1:53 am
Thanks for replying sir
But i want to have 2 different columns i.e., Name & SubjectWiseMarks.
But for column Name i want distinct .
October 24, 2013 at 5:27 am
Hi venkidesaik,
I thought below query met you requirement.Have a look into this ad try it.
;with cte as
(
select ROW_NUMBER() over ( order by name) as RN,* from MySampleTable
)
select
(case when RN in (select min(RN) from cte group by Name) then Name else null end) as Name
,SubjectWiseMarks from cte
[font="Courier New"]
NameSubjectWiseMarks
AAA Maths-100
NULL Physics-90
NULL Chemistry-80
BBB Maths-90
NULL Physics-80
CCC Maths-80[/font]
***********************************
;with cte as
(
select ROW_NUMBER() over ( order by name) as RN,* from MySampleTable
)
select
(case when RN in (select min(RN) from cte group by Name) then Name else ' ' end) as Name
,SubjectWiseMarks from cte
[font="Courier New"]NameSubjectWiseMarks
AAA Maths-100
Physics-90
Chemistry-80
BBB Maths-90
Physics-80
CCC Maths-80 [/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply