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 Rows to Columns

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure   /   2 Comments
28
May
2014

SQL Rows2Columns  ALTER PROCEDURE Rows2Columns @tableName varchar(50), @columnName varchar(50) AS BEGIN create table #temp(Id int) insert into #temp(Id) values(1) declare @DATA table(RowId int identity(1,1), value nvarchar(50)) insert into @DATA exec('select cast(' + @columnName + ' as varchar(50)) from ' + @tableName) declare _cursor CURSOR FOR(select RowId, value from @DATA) declare @index int, @value nvarchar(50) OPEN _cursor FETCH NEXT FROM _cursor into @index, @value WHILE @@FETCH_STATUS = 0 BEGIN exec('ALTER TABLE #temp ADD.

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 delete from join

Posted by: RcBuilder  /   Category: SQL / SQL Function / Stored Procedure   /   1 Comment
11
May
2014

sql delete from join declare @tbl1 table(Id int, Name nvarchar(30)) insert into @tbl1 values(1, 'ROBY') insert into @tbl1 values(2, 'AVI') insert into @tbl1 values(3, 'RON') insert into @tbl1 values(4, 'KOBI') insert into @tbl1 values(5, 'YOSSI') insert into @tbl1 values(6, 'GALIT') insert into @tbl1 values(7, 'RON') insert into @tbl1 values(8, 'RON') insert into @tbl1 values(9, 'ROBY') declare @tbl2 table(Id int, Name nvarchar(30)) insert into @tbl2 values(1, 'ROBY') insert into.

Read more

SQL recursive using CTE

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   1 Comment
08
May
2014

SQL recursive using CTE Data source Id Name Parent 1 'ROOT ITEM NULL 2 'PARENT 1' '1' 3 'PARENT 2' '1' 4 'PARENT 3' '1' 5 'PARENT 1' 'SON 1 2' 6 'PARENT 1' 'SON 2 2' 7 'PARENT 1' 'SON 3 2' 8 'PARENT 2' 'SON 1 3' 9 'PARENT 2' 'SON 2 3' 10 'PARENT 2' 'SON 3 3' 11 'PARENT 2' 'SON 4 3' 12 'PARENT.

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