Table merge to itself

Question

I created a table "Test"

create table test
(
  id int identity(1,1) not null,
  correlation int,
  data varchar(max)
)

Below are data of the table

insert into test(correlation,data) values(1,'x0')
insert into test(correlation,data) values(1,'x1')
insert into test(correlation,data) values(2,'z1')
insert into test(correlation,data) values(2,'z2')
insert into test(correlation,data) values(3,'a')
insert into test(correlation,data) values(4,'b')
insert into test(correlation,data) values(5,'c')

I need to display data on the web page and to connect table to itself on correlation and to do paging

For example if I have two records with the same correlation (1) I need to display two rows as one row with the data as Current Data and Previous Data. In example below current data will be x1 and previous will be x0.

Before

 Correlation  Data
    1         x0    
    1         x1

After

Correlation     Previous Data   Current Data
1                  xo             x1

If correlation has only one row , than previous correlation in result will be null.

Currenly I did paging in Linq and it's working but I am afraid that in future it will cost performance problem.

Can sameone can help me with SQL .

Is there other good solution for this problem.

So each `Correlation` has just two `data` entries the first will be `Previous` and the second will be `Current` ?? and What about the correlations that have only one `data` entry?? — Mahmoud Gamal, Nov 24 '11 at 13:09
If correlation have only one data entry than Current will be with value and previous will be null — Gregory Nozik, Nov 24 '11 at 13:21
You want to pivot data on your correlation data. (PIvot is the function your looking for several EXAMPLES can be found though search) For example: http://stackoverflow.com/questions/7182106/sql-server-pivot-perhaps — xQbert, Nov 24 '11 at 13:21
@xQbert, But pivot it is about turning the unique values from one column to be a headers for new columns, but in this case how this would be applied??? — Mahmoud Gamal, Nov 24 '11 at 13:29
Fair enough: How do I know what data is previous and what data is current then? or does it matter.. Looking at 3 a.. how do I know it's Previous or "current"? Do I assume 1 entry means previous or something? — xQbert, Nov 24 '11 at 13:41

Mikael Eriksson · Accepted Answer · 2011-11-24T13:56:35.887

2

;with C as
(
  select correlation,
         data,
         row_number() over(partition by correlation order by id desc) as rn
  from @test
  where SomeColumn > 10 -- Where clause goes here (if possible)
)  
select C1.correlation,
       C2.data as [Previous Data],
       C1.data as [Current Data]
from C as C1
  left outer join C as C2 
    on C1.correlation = C2.correlation and
       C2.rn = 2
where C1.rn = 1

Result:

correlation Previous Data Current Data
----------- ------------- ------------
1           x0            x1
2           z1            z2
3           NULL          a
4           NULL          b
5           NULL          c

edited Nov 24 '11 at 13:56

answered Nov 24 '11 at 13:32

Mikael Eriksson

136,425
22
210
281

@GregoryNozik - I was a bit quick to post. Updated answer. Test here http://data.stackexchange.com/stackoverflow/q/119042/ – Mikael Eriksson Nov 24 '11 at 13:38
In real live my test table is composes from many tables. Do you think it will work fast ? – Gregory Nozik Nov 24 '11 at 13:42
@GregoryNozik - Speed of a query is dependent on a lot of stuff, indexes and how much data you have for instance. If you have anything that filters the data you want, you should preferably put the where clause in the CTE part to avoid ranking on rows you actually don't need. – Mikael Eriksson Nov 24 '11 at 13:50
`C` is a CTE. I will add a where clause where it will do most good :) – Mikael Eriksson Nov 24 '11 at 13:55
Could you helped me to optimized this query – Gregory Nozik Nov 24 '11 at 14:28
@GregoryNozik - You should put that out as a new question giving all a chance to help. Provide the query with table structure including existing indexes and expected number of rows in the table. Describe also what performance you have and what performance you need. – Mikael Eriksson Nov 24 '11 at 15:04

Table merge to itself

1 Answers1