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 …

The query is asked in my recent interview and the same I want to share with you. If anyone has better solution(corrected by Sandeep Walia)  please share here:
declare @dt datetime
set @dt=’1/1/2010′ –Want to know January’s Last Monday Date
SELECT dateadd(d,-datepart(dw,DATEADD(d, -datepart(d,dateadd(mm,1,@dt)),dateadd(mm,1,@dt))-1)+1,DATEADD(d, -datepart(d,dateadd(mm,1,@dt)),dateadd(mm,1,@dt)))
Tweet This Post

Sql server stores date as yyyy/dd/mm and returns with time.
use following query:
Select CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘/’ + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + ‘/ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS DATE
Result will be : in mm/dd/yyy
Tweet This Post

Simply, you can use ‘Insert Into‘
ex:
Create Table newTable (id VarChar(10))
Insert Into newTable Select id from oldTable
Tweet This Post