This question is asked in recent interview, following is the scenario:

Q. I have a following table :

I want results as :

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 @tbl

)

SELECT case when rowid=1 then university else null end university,case when rowid=1 then Utotals else null end Utotals,status,statuscnt

FROM cte

Popularity: 35% [?]

Tweet This Post

Related interview questions

• Create dynamic query with dynamic joins
• I need to view the number of tables existing under one particular Owner. Is it possible? If so, pl give the SQL query for this?
• Change date format mm/dd/yy to dd/mm/yy using query?
• When is the results table for the query in a DECLARE CURSOR statement created?
• What is File Status in VSAM?