Does that really work Ayesha??...Did you check? 😛

That is a very nice query ColdCoffee. Great job with the Partitions.

Use the Splitter as a function and Cross Apply on the Table to get the desired results. Post DDL and Sample data for a working example.

There are many ways to do it. Here are a few....you can choose which one to use depending upon your requirement:

--Creating Table

Create Table Ex

(PID int,

ID int,

FileName Varchar(10),...

This is a little simpler but on the same lines as ColCoffee:

--===== Create the temporary test table #TABLE1 with

CREATE TABLE TABLE1 (

PK INT PRIMARY KEY,

Class NVARCHAR(2)

);

--===== Create the temporary...

Thank you very much G.

I'll Google both types of Hierarchies.

Yes, you may be correct.

This is just tailor made to fit my requirement. But, its good that you have brought it to my notice.

Can you post a sample data set...

Well, no answers yet.

But, I've got good news. I solved it using Recursive CTE. This is the solution:

Declare @loginId int = 2

;With CTE

As

(

--Anchor Member Definition

Select EmpId From Hierarchy_Test Where SupervisorId...

Well, no answers yet.

But, I've got good news. I solved it using Recursive CTE. This is the solution:

Declare @loginId int = 2

;With CTE

As

(

--Anchor Member Definition

Select EmpId From Hierarchy_Test Where SupervisorId...

DataAnalyst110 (7/3/2012)

---

Hi Vinu - Thanks for your reply. What is the simpler query? To put make the cte a subquery in the from clause? That is the only thing I...

Sorry, for the late reply.

You can create a Calender Table and JOIN the Calender Table with the Table "MetricValues" to get the Desired Output as follows:

--Creating Table

Create Table MetricValues (...

David Webb-200187 (6/29/2012)

---

I always learn something new every time I try one of these.

Thanks for jumping in, Lynn.

Had a syntax error on the select, but this runs:

SELECT ...

This could be another way of doing it:

Select Activity, COUNT(Activity) As Fees

From

(Select *, ROW_NUMBER() Over (Partition By Stud_Id, Activity Order By Stud_Id) As rn From TestingCase_Act) As a

Where rn...

How about making it a little simple by avoiding all those CTEs like this:

CREATE TABLE PVT_AVG

(

Stu_IDCHAR(10)NOT NULL,

Adm_ExamCHAR(25)NOT NULL,

Adm_Exam_DateDateNOT NULL,

Adm_Exam_ScoreCHAR(6)NOT NULL

)

INSERT INTO PVT_AVG

(

Stu_ID,

Adm_Exam,

Adm_Exam_Date,

Adm_Exam_Score

)

VALUES

('123456', 'Adm_MCT', '2009-01-01', '21'),

('123456', 'Adm_MCT', '2010-03-01', '23'),

('987654', 'Adm_GPA', '2008-04-15',...

It can be done in a no. of ways depending on whether the no. of columns in the query would be static or Dynamic. Following are a few ways of...