Transition in Column2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transition in Column2

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-14 : 12:33:31

Hello All,

Time1 Black
Time2 Black
Time3 White
Time4 Purple
Time5 White
Time6 Blue

Say I wanted to know everytime there has been a transition from a color to white.
So Black to white or Purple to white in column 2.

Column1 Time is just used to sort. The change in column two will always be text.

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 12:39:18

Would this work for you? You need to be on SQL 2005 or higher for this to compile.

;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (ORDER BY TIME) AS RN
	FROM
		YourTable
)		
SELECT
	t1.Time WhiteTime,
	t1.Color WhiteColor,
	t2.Time PriorTime,
	t2.Color AS PriorColor
FROM
	cte t1
	INNER JOIN cte t2 ON
		t1.rn = t2.rn+1
WHERE
	t1.Color = 'White' AND t2.Color <> 'White'

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-14 : 12:52:55

Hello Sunita,

What is cte? My table name is 'Events' and I have my database set to my database.

The column name is 'EventType' and for WhiteTime and WhiteColor - those are variables? I assume they can be called anything.

Also the FROM statement:
FROM
cte t1
INNER JOIN cte t2 ON
t1.rn = t2.rn+1
t1 and t2 are both Events table correct? Just inner joined with itself and a ID + 1?
I assume then t2 is Events_1?

Sry if you could help me with the syntax I would appreciate it.

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 13:33:47

CTE is acronym for Common Table Expression (http://msdn.microsoft.com/en-us/library/ms190766.aspx). You can think of it as a virtual table in some ways similar to a subquery.

In the query that I posted, the green part is creating the CTE. The rest of it is using that CTE. So the cte just has the same columns as your original table and an additional column that is a row number order by the value in the time column.

The query does not update anything, it just selects data, so you can run it and experiment to see what it does.


;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (ORDER BY TIME) AS RN
	FROM
		YourTable
)		
SELECT
	t1.Time WhiteTime,
	t1.Color WhiteColor,
	t2.Time PriorTime,
	t2.Color AS PriorColor
FROM
	cte t1
	INNER JOIN cte t2 ON
		t1.rn = t2.rn+1
WHERE
	t1.Color = 'White' AND t2.Color <> 'White'

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-18 : 17:23:11

Sunita,

Is there a way to check how the CTE tables look? I am having no records displayed and I wanted to look at the CTEs to see if my inner join is wrong, etc..

==========================
Pain is Weakness Leaving the Body.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-18 : 17:52:54

You can run the inner query to see what the CTE is producing:

SELECT
   *,
   ROW_NUMBER() OVER (ORDER BY TIME) AS RN
FROM
   YourTable

The query will be joining rows with adjacent row numbers (column RN).

If you still don't get the results you are looking for, can you post some sample data that demonstrates the problem?

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-19 : 14:38:45

Hey Sunita,

I got this problem worked out. Thanks again! CTEs are really nifty.

One syntax question - can they work in a table valued function and stored procedures?

Thanks,
Dasman

Dasman

==========================
Pain is Weakness Leaving the Body.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-19 : 15:56:24

If you liked CTE's, you should really look up recursive CTE's. They are the coolest things, that let you do a lot of things that would be impossible or next to impossible in SQL. http://msdn.microsoft.com/en-us/library/ms186243.aspx

You can use CTE's in stored procs and functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources