T-sql

1

Hi Folks,

can somebody help me to get the expected result. Thanks

drop table if exists #tableName
drop table if exists #ExpectedResult

create table #tableName(pname varchar(100), pcity varchar(100), cellnumber BIGint , pgender char(1) , premark varchar(100) , premark2 INT)
create table #ExpectedResult(pname varchar(100), pcity varchar(100), cellnumber BIGint , pgender char(1) , premark varchar(100) , premark2 INT ,pcomment1 varchar(100),pcommentn int)

insert into #tableName(pname,pcity,cellnumber,pgender,premark,premark2)
select 'robert', 'Austria' ,7823676 ,'M', 'yeteh' ,1 UNION ALL
select 'Troat', 'Australia' ,8728378722 ,'M', 'uieye' ,2 UNION ALL
select 'john' , 'Malawi' ,762783676 ,'M', 'mithel' ,3 UNION ALL
select 'Ricky' , 'Singapore' ,983763676 ,'M', 'rabada' ,4 UNION ALL
select 'glen', 'India' ,6762786 ,'M', 'maxvel' ,5 UNION ALL
select 'hyden' , 'India' ,29479247 ,'M', 'smith' ,6 UNION ALL
select 'Troat', 'Australia' ,8728378722 ,'M', 'bolt' ,7 UNION ALL
select 'john' , 'Malawi' ,762783676 ,'M', 'marsh' ,8 UNION ALL
select 'glen', 'India' ,6762786 ,'M', 'Travis' ,9

insert into #ExpectedResult(pname,pcity,cellnumber,pgender,premark,premark2,pcomment1,pcommentn)
select 'robert', 'Austria' ,7823676 ,'M', 'yeteh' ,1 ,null ,null UNION ALL
select 'Troat', 'Australia' ,8728378722 ,'M', 'uieye' ,2 ,'bolt' ,7 UNION ALL
select 'john' , 'Malawi' ,762783676 ,'M', 'mithel' ,3 ,'marsh' ,8 UNION ALL
select 'Ricky' , 'Singapore' ,983763676 ,'M', 'rabada' ,4 ,null ,null UNION ALL
select 'glen', 'India' ,6762786 ,'M', 'maxvel' ,5 ,'Travis' ,9 UNION ALL
select 'hyden' , 'India' ,29479247 ,'M', 'smith' ,6 ,null ,null

select * from #tableName

select * from #ExpectedResult

2 
WITH GroupOrder
AS
(
	SELECT pname, pcity, cellnumber , pgender, premark, premark2
	,ROW_NUMBER() OVER (PARTITION BY pname, pcity, cellnumber , pgender ORDER BY premark2) AS rn
	FROM #tableName
)
SELECT pname, pcity, cellnumber , pgender
	,MAX(IIF(rn = 1, premark, NULL)) AS premark
	,MAX(IIF(rn = 1, premark2, NULL)) AS premark2
	,MAX(IIF(rn = 2, premark, NULL)) AS pcomment1
	,MAX(IIF(rn = 2, premark2, NULL)) AS pcommentn
FROM GroupOrder
GROUP BY pname, pcity, cellnumber , pgender
ORDER BY premark2;
1 Like