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
 convert to other row

Author  Topic 

logpop
Starting Member

16 Posts

Posted - 2013-04-22 : 16:56:11
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-22 : 17:04:07
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 - 2013-04-22 : 19:40:14
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.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 21:24:05
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 - 2013-04-23 : 23:30:31
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-24 : 01:24:59
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 - 2013-04-24 : 07:50:14
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

52326 Posts

Posted - 2013-04-24 : 08:34:50
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
   

- Advertisement -