Generate N Level Product List Using CTE

Hello Friends,
Here I show you example which will generate navigation list for product through N level. Please check below query for generate N Level product list.

Query:-
-----------------------------------------------

DECLARE @Category TABLE
(
CategoryID INT
,Name VARCHAR(100)
,ParentID INT
)

INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 1,'Mobile',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 2,'Nokia',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 3,'Samsung',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 4,'Headphone',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 5,'Nokia N70',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 6,'Nokia N72',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 7,'IBall',4)

;WITH CTE(CategoryID,Name,ParentID,level,ParentName)
AS
(
SELECT CategoryID,Name,ParentID,0,cast('.' + Name + '.' AS VARCHAR(MAX)) FROM @Category WHERE ParentID=0
UNION ALL
SELECT C.CategoryID,C.Name,CT.ParentID,level + 1,CAST (CT.ParentName + '.' + C.Name AS VARCHAR(MAX)) FROM @Category C
INNER JOIN CTE CT ON CT.CategoryID=C.ParentID
)
SELECT SPACE(level * 4) + Name AS Products FROM CTE ORDER BY ParentName

Output
-------------------

Common Table Expression Example

Hello Friends,
Here I put the sample code example using CTE.

--Generate Fibonacii Series using CTE


;WITH FibonaciiSeries(N,F1,F2,F3)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT),CAST(0 AS BIGINT),CAST(1+0 AS BIGINT)
UNION ALL
SELECT N+1, F2,F3,F2+F3 FROM FibonaciiSeries WHERE N<92
)
SELECT * FROM FibonaciiSeries

--OUTPUT
-------------------------



--Find Factorial

;WITH Factorial(N,Number)
AS
(
SELECT CAST (1 AS BIGINT), CAST (1 AS BIGINT)
UNION ALL
SELECT N+1,CAST ((N+1)*(Number) AS BIGINT) FROM Factorial WHERE N<10
)

SELECT * FROM Factorial

--OUTPUT
-------------------------

Find the second highest salary for each department

Hello Friends,
Here I post one TSQL Challenge. I hopes it will help you.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

http://beyondrelational.com/blogs/tcb/archive/2009/10/13/tsql-beginner-s-challenge-1-find-the-second-highest-salary-for-each-department.aspx

TSQL Challenge Solved-

TSQL:-
SET NOCOUNT ON
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)


SELECT * FROM @Employees

;WITH SecondHighestSalary
AS
(
SELECT EmployeeID,EmployeeName,Department,SALARY AS SALARY,RANK() over (partition by Department order by salary) AS SalaryNumber FROM @Employees
)
SELECT EmployeeID,EmployeeName,Department,SALARY FROM SecondHighestSalary WHERE SalaryNumber=2



OUTPUT:-

Concatenate multiple rows delimited by comma

Hello frnds,
Most of people need to select multiple row and convert it into CSV format.
Here I came with solutions.

For Example:-

   1:  Declare @tbl table (id int,sid int)

   2:   

   3:  insert into @tbl

   4:  select 1,11

   5:  union

   6:  select 1,12

   7:  union

   8:  select 1,13

   9:  union

  10:  select 2,21

  11:  union

  12:  select 2,22

  13:   

  14:  SELECT * FROM @tbl

  15:   

  16:  --With CTE:-

  17:  --------------------

  18:  ;WITH RowRank

  19:  AS

  20:  (

  21:  SELECT id,CAST(sid AS VARCHAR) sid,ROW_NUMBER() OVER(PARTITION BY id order by id) AS Row FROM @tbl

  22:  )

  23:  ,Concat(id,sid,Row)

  24:  AS

  25:  (

  26:  SELECT id,CAST(RowRank.sid AS VARCHAR),Row FROM RowRank WHERE Row=1

  27:  UNION ALL

  28:  select RowRank.id, CAST(Concat.sid + ', ' + RowRank.sid AS VARCHAR ) ,RowRank.Row

  29:  from RowRank

  30:  inner join Concat on RowRank.id = Concat.id and RowRank.Row -1 = Concat.Row

  31:   

  32:  )

  33:  ,CommaSeperatedResult (id, sid)

  34:  as

  35:  (

  36:  select Concat.id, Concat.sid

  37:  from Concat

  38:  join (select id, max(Row) max_row from RowRank group by id) max_row

  39:  on Concat.id = max_row.id and Concat.Row = max_row.max_row

  40:  )

  41:  SELECT * FROM CommaSeperatedResult ORDER BY ID

  42:   

  43:   

  44:  --WITH FOR XML PATH:-

  45:  ------------------------------

  46:  select distinct id,

  47:  replace

  48:  (

  49:  (

  50:  select cast(sid as varchar)+',' from @tbl t where t.id=t1.id for XML path('')

  51:  ) +'$',',$',''

  52:  )

  53:  as sid

  54:  from @tbl t1

  55:   

Output is:-
----------------

Using common table expressions (CTE) to generate sequences

Hello frnds,
One of the best enhancements in T-SQL with SQL Server 2005 was Common Table Expressions(CTEs). CTEs are very helpful in writing more readable and manageable queries. The good things don’t end here; self-referencing CTEs are a very powerful method of implementing recursion in SQL queries. In this post, I will present a few examples of generating sequences using CTEs.
The following statements create a number sequence from 1 to 10.

Example:-

   1:  Declare @start int, @end int

   2:  Select @start=1, @end=10

   3:   

   4:   

   5:  ;WITH CTESequence(number)

   6:  AS

   7:  (

   8:  SELECT @start

   9:  UNION ALL

  10:  SELECT number +1 FROM CTESequence

  11:  WHERE number<@end

  12:  )

  13:  SELECT * FROM CTESequence

Output is :-