ProgrammingOcean

drop table ##temp create table ##temp (id char(3) ,marks int ) create table ##temp2 (id char(3) ,marks int ) insert into ##temp(id,marks) ----Here we are inserting duplicate select '001',50 ----records for each ID union all select '001',60 union all select '002',66 union all select '002',88 union all select '003',92 union all select '003',64 union all select '004',44 union all select '005',67 ----Here we are getting the distinct records and insert then into another Tempory table insert into ##temp2 select distinct id,max(marks) from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) group by id ---And delete those duplicate records from original Table delete from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) ---And again inser the inserted reocrds from temporary Table insert into ##t

Blogger Buzz: Blogger integrates with Amazon Associates

We can use this clause to sum/count/avg/max/min so on. This clause will give you the output as detail and summary which is based on the fields you want to summarize. select * from #temp order by student compute sum(marks) by student in above compute by clause, you must specify the field you want to sum in Compute clause and specify the field in By clause based on which field you need to compute. Very important thing is you must specify the Order By clause in which specify the fileds whatever you specify in By clause in Compute clause. The output of above query is, When we try with max,min,avg, the query and output would be as shown below, Using Max() select * from #temp order by student compute max ( marks ) by student Using Min () select * from #temp order by student compute min ( marks ) by student Using Avg() select * from #temp order by student compute avg ( marks ) by student

We can use rowcoun t sql property to set the number of rows to be shown in the output. for an example, lets say there are 10 records in a table, if we set the rowcount to 5 then when retrieve records from that table, only 5 records will be shown. if you rowcount to 0 then all records will be retrieved and shown in output. SET ROWCOUNT 5 SELECT ref_num FROM tbl_po_master in above example only 5 rows have been retrieved and shown in output as we set the rowcount to 5.

In SQL Server you can retreive the field names as shown below, SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name='Table_Name')

There is way find what are the parameter list for a storedprocedure in sql server rather find them by open individually. SELECT PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='AddDefaultPropertyDefinitions' Hope this would be very useful for developers who are working with database.

Here it is the procedure to Split the words using comma seperator. still you can use different character for split instead of comma(','). Here i m using 'E,l,e,p,h,a,n,t' as word with comma characters. so the output should be 'E','l','e','p','h','a','n','t'. Declare @name as varchar(20) Declare @i as int Declare @char as char Declare @word as varchar(20) select @name='E,l,e,p,h,a,n,t' set @word='' set @i=1 while @i<=len(@name) begin set @char=substring(@name,@i,1) if @char<> ',' begin set @word=@word+@char end else if (@char=',' and @i<>len(@name)) begin print @word set @word='' end ---Print the last word if @i=len(@name) begin print @w

In SQL Server there is a built-in function called Datepart() which is takes 2 paramaters which are return date option and date value. for the 1st paramater pass the date option as 'dw' and for second parameter pass the date value as shown below, SET dateformat dmy Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek if you execute this query, output will be, in SQL Server, by default the week start with 'Monday' which is 1. so in this example, the week is Tuesday. So the number of the Tuesday is 2. You can check, what is default start week number by using @@DATEFIRST. Select @@DATEFIRST Since SQL Server default start week number is 1(Monday), it is giving 1 in output. Default Value for Week in SQL Server, Monday - 1 Tuesday - 2 Wednesday - 3 Thursday

There is a built-in function call DateName() in SQL Server to get the Weekday Name. This function takes 2 parameters in which first is return date option whereas second one date value from which you want to get the weekday name. To get the weekday name you have to specify the date option as 'dw' . for example, set dateformat dmy SELECT DATENAME(dw,'09/03/2010') Weekday in this example '09' is day of march. so if you use this sql function as i given above, it will return the exact name of the weekday. in output, it is give you week day name.

There is a built-in function called datename() for find the month name from given date. for example:

In this post i thought to expalin how to get one column data in a single row with comma separator. as i shown shown below a table have one column call Choice which have 4 rows. So our task is how to bring up all these 4 rows into one single row with comma separator. We can do this in 2 methods, Using COALESCE() function Using ISNULL() Function Actually both are playing same role. 1. Using COALESCE() function As shown below in the SQL Query below, COALESCE() function takes 2 parameters. It is check whether passed field value is Null or not if it is null then place with blank whereas if the field value is not null then concatenate value with comma. 2. Using ISNULL() Function This query also working similar to COALESCE() function. here value 'A' repeating twice. so how to get distinct value from this? here it is the SQL query for that, We just write subquery to filter only distinct values and using that as a Table for Outer query. So here only gives A,B,C.