RcBuilder@walla.com
call centre: 054-5614020

SQL Categories

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
27
Nov
2014

SQL Categories - OPTION 1 - declare @tbl table(Id int,Name nvarchar(50),Parent int,SubCategories nvarchar(500)) insert into @tbl select 1,'111',0,'' union all select 2,'222',1,',2,5,6,7,13,14,' union all select 3,'333',1,'' union all select 4,'444',1,'' union all select 5,'555',2,'' union all select 6,'666',2,'' union all select 7,'777',2,',7,13,14,' union all select 8,'888',3,'' union all select 9,'999',3,'' union all select 10,'aaa',3,'' union all select 11,'bbb',3,'' union all select 12,'ccc',3,'' union all select 13,'ddd',7,'' union all select 14,'eee',7,'' ------------------------------------ declare @Id int set.

Read more

SQL COLLAPSE example

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
20
Oct
2014

SQL COLLAPSE - multiple rows to single value we can use the COLLAPSE sql function to concatenation values into a single string data declare @tbl2 table(Name nvarchar(10), Quantity int, CreatedDate smalldatetime) insert into @tbl2 SELECT 'Roby', 100, '2009-12-05' UNION ALL SELECT 'Avi', 40, '2011-11-05' UNION ALL SELECT 'Avi', 60, '2010-05-03' UNION ALL SELECT 'Avi', 15, '2009-06-11' UNION ALL SELECT 'Ron', 50, '2010-04-11' UNION ALL SELECT 'Roby', 77,.

Read more

SQL COUNT NULLIF

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
28
Sep
2014

SQL COUNT NULLIF // nullif return null if the condition is true // use this technique to count multiple counters in a single group by clause declare @tbl table(Id int, isOK bit, Grade tinyint) insert into @tbl select 1, 1, 50 union all select 1, 1, 75 union all select 4, 0, 25 union all select 6, 1, 54 union all select 6, 0,.

Read more

Split SQL FUNCTION

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
23
Sep
2014

Split SQL FUNCTION  create FUNCTION Split ( @String nvarchar (MAX), @Delimiter nvarchar (5) = ',' -- use the keyword 'DEFAULT' in order to use the default parameter ) RETURNS @ValueTable table ( nvarchar(50)) AS BEGIN if(RTRIM(LTRIM(@String)) = '') return declare @NextString nvarchar(50) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String =.

Read more

SQL delete duplicates

Posted by: RcBuilder  /   Category: SQL / SQL Function / Stored Procedure / System Function   /   No Comments
19
May
2014

SQL delete duplicates declare @temp table(RowId int identity(1,1), id int, Name varchar(20)) insert into @temp select 123,'ROBY' union all select 344,'AVI' union all select 310,'ITAY' union all select 155,'SHLOMI' union all select 123,'SHIRLY' union all select 310,'TAMIR' union all select 167,'RAMI' delete from @temp where RowId in(select MAX(RowId) from @temp group by id having COUNT(*) > 1) select * from.

Read more

SQL COALESCE – CONCAT COLUMNS

Posted by: RcBuilder  /   Category: SQL / System Function   /   No Comments
04
May
2014

USE COALESCE TO CONCAT COLUMNS VALUES TO A SINGLE STRING SEPERATED BY ','  declare @columns table(name nvarchar(MAX)) insert into @columns select 'Column1' UNION ALL select 'Column2' UNION ALL select 'Column3' UNION ALL select 'Column4' UNION ALL select 'Column5' UNION ALL select 'Column6' declare @str_columns nvarchar(MAX) select @str_columns = COALESCE(@str_columns + ', ', '') + name from @columns select @str_columns result : Column1, Column2, Column3, Column4, Column5, Column6 GET SPECIFIC TABLE.

Read more
12
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים