This question is asked in recent interview, following is the scenario:
Q. I have a following table :

University
UtoTal
Status
StatusCnt

U1
100
Pending
20

U1
100
Active
20

U1
100
Deferred
60

U2
60
Pending
20

U2
60
Active
20

U2
60
Deferred
20

I want results as :

University
UtoTal
Status
StatusCnt

U1
100
Pending
20

Active
20

Deferred
60

U2
60
Pending
20

Active
20

Deferred
20

Ans: At the time of interview I wasn’t easy with the answer but I wrote a query, but interviewer not satisfied.
I got the following proper query/solution from one of my colleagues Mr. sandeep Walia:
declare @tbl table(university varchar(100),Utotals int, status varchar(100),statuscnt int)
insert into @tbl(university,Utotals,status,statuscnt)
select ‘u1′,100,’pending’,20
union all
select ‘u1′,100,’Active’,20
union all
select ‘u1′,100,’Deferred’,60
union all
select ‘u2′,60,’pending’,20
union all
select ‘u2′,60,’Active’,20
union all
select ‘u2′,60,’Deferred’,20
select * from @tbl;
with cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY university ORDER BY university DESC) AS RowID,
*
FROM …

Q. Here is the scenario:

declare @tt table(col1 varchar(10), joinexp varchar(100))
insert into @tt
select ‘c1′,’join table2 on table1.id = table1.id’
union all
select ‘c2′,’join table3 on table2.id = table1.id’
union all
select ‘c3′,’join table4 on table3.id = table1.id’
union all
select ‘c4′,’join table5 on table5.id = table1.id’
union all
select ‘c5′,NULL
union all
select ‘c6′,NULL
declare @str varchar(1000)
set @str = ‘Select c1,c4,c5,c8,c9 from table1′
declare @tt table(col1 varchar(10), joinexp varchar(100))insert into @ttselect ‘c1′,’join table2 on table1.id = table1.id’union allselect ‘c2′,’join table3 on table2.id = table1.id’union allselect ‘c3′,’join table4 on table3.id = table1.id’union allselect ‘c4′,’join table5 on table5.id = table1.id’union allselect ‘c5′,NULLunion allselect ‘c6′,NULL
declare @str …