CODEWITHSUNDEEP
sqlsql-server
J Cooper
J Cooper

Reputation: 5008

How do I transform table- see example?

If someone suggests a better question title, I will change it, but that's the best I could come up with.

I am using SQL Server 2005

Is there a good way to transform table 'table_1' to 'table_2' using SQL.

Edit: I don't necessarily want to create a new table, I just want to represent it in a query as so that it will look like 'table_2'

table_1:

 a   b     
 1   TRUE   
 2   FALSE  
 4   TRUE   
 7   FALSE  
 9   TRUE   
10   FALSE

table_2:

x    y   z
1    2   TRUE
2    4   FALSE
4    7   TRUE
7    9   FALSE
9   10   TRUE

The reason I want to do this is so that if I have table_3:

table_3:

id    m
a     3
b     4
c     8

I would like to query against table_2 (transformed table_1) like this:

SELECT table_3.id
FROM table_3
FROM table_2
WHERE m BETWEEN x AND y
AND z = TRUE

I'll answer the following questions a head of time: 1. I can not change the table design, nor did I design the table. 3. I have good reason not to process this on client side with c# or another language.

thanks

Upvotes: 0

Views: 91

Answers (1)

Jacob
Jacob

Reputation: 78920

Try a self-join:

select t1.a, min(t2.a), t1.b
from table_1 t1
    inner join table_1 t2 on t2.a > t1.a
group by t1.a, t1.b

Upvotes: 1

Related Questions