SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 convert to other row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

logpop
Starting Member

16 Posts

Posted - 04/22/2013 :  16:56:11  Show Profile  Reply with Quote
how do i convert this:
col1 col2 col3
1 A C
2 B D
3 C A
4 D A

to this:

col1 col2 col3
1 A 3
2 B 4
3 C 1
4 D 1

thanks!:)

Lamprey
Flowing Fount of Yak Knowledge

4363 Posts

Posted - 04/22/2013 :  17:04:07  Show Profile  Reply with Quote
There are bunch of way to do that. Here is a probably the most inefficient way:
DECLARE @Foo TABLE (col1 int, col2 CHAR(1), col3 CHAR(1))

INSERT @Foo VALUES
(1, 'A', 'C'),
(2, 'B', 'D'),
(3, 'C', 'A'),
(4, 'D', 'A')

SELECT 
	col1,
	col2,
	(SELECT TOP 1 col1 FROM @Foo AS B WHERE B.col2 = A.col3)
FROM
	@Foo AS A
Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 04/22/2013 :  19:40:14  Show Profile  Reply with Quote
it works. how inefficient is it? table is not big, currently 300 entires, i doubt it will get much bigger, maybe max 10.000 entries theoretically. how much time would that take? can i measure its speed somehow in ms sql server management studio or somewhere? when i run it firs time it takes longer, but repeated runs it says it took 0.0 seconds.

Edited by - logpop on 04/22/2013 19:41:51
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 04/22/2013 :  21:24:05  Show Profile  Reply with Quote
You can turn on the execution plan (control-M) and run it. There will be another pane next to the results window that shows the execution plan. You can hover over or right click and select properties on each of the steps to see all kinds of details about cpu time, IO cost etc. Or you can SET STATISTICS PROFILE ON and run it (or SET STATISTICS TIME ON or SET STATISTICS IO ON etc. each shows different information).

That said, if you have only 300 entries, I wouldn't worry about performance.
Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 04/23/2013 :  23:30:31  Show Profile  Reply with Quote
I set
SET STATISTICS TIME ON
go
SET STATISTICS IO ON
GO
and i get total time it takes to execute whole query. its a big query, i use WITH keyword to kinda structure it into "functions"

I found out deleting this line:
(SELECT TOP 1 col1 FROM @Foo AS B WHERE B.col2 = A.col3)
CPU time goes down from 1400 ms to 140 ms.
and this:
Table 'Worktable'. Scan count 105754, logical reads 322152
changes to this:
Table 'Worktable'. Scan count 2368, logical reads 33860

One second is kinda unacceptable. ill have to find something else. if it cant be done with sql efficiently ill deal with it in ASP.NET program.
Maybe i should mention that instead of @Foo im using a "view" created with WITH keyword. selecting all items from that view
takes 156 ms.

hmm or is 2 second query normal time for for example online store? lets say it takes 2 seconds to list all online store product categories, thats not good right? or do meaybe sql servers extensively use cache so 2 seconds is not that bad? i dont imagine it goez into db for every client viewing site but uses some caching system?

Edited by - logpop on 04/23/2013 23:47:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/24/2013 :  01:24:59  Show Profile  Reply with Quote
what about a join?

SELECT t.col1,
t.col2,
t1.col1
FROM Table t
INNER JOIN table t1
ON t1.col2  = t.col3


convert it to LEFT JOIN if you wont have a value for col3 always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 04/24/2013 :  07:50:14  Show Profile  Reply with Quote
Thanks dude it works much faster, 300 ms instead of 1400 ms. Now I just have to understand it. Here is my explanation tell me if I'm correct:

We take these two tables:
t:
(1, 'A', 'C'),
(2, 'B', 'D'),
(3, 'C', 'A'),
(4, 'D', 'A')

t1:
(1, 'A', 'C'),
(2, 'B', 'D'),
(3, 'C', 'A'),
(4, 'D', 'A')

and combine them into new temporary table using this algorithm:

for each row tRow of t do this:
t1Row = (find a row in t1 which has same col3 as tRow.col2)
if found create new row in this way:
newRow = (tRow.col1, tRow.col2, t1Row.col1)
and add newRow to result dataset.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/24/2013 :  08:34:50  Show Profile  Reply with Quote
yep...it uses the same table twice (self join) based on the relationship t1.col2 = t.col3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000