Amalgamating data from the same table

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

Amalgamating data from the same table

Author

Topic

Liza-Jane
Starting Member

3 Posts

Posted - 2013-09-02 : 05:44:09

Hi
I've got a series of statements to run on a single table which should amalgamate one of the fields onto one of the records - and in the main it does. Except when it gets to the last two data fields to be joined.

There are 3 stages - 1. update the max_ptr;
2. concatenate the data;
3. delete the line.
I repeat these 3 until the last one returns 0 deletions.

The data in the table looks like this:
Lic Code TextData max_ptr min_ptr ID
LIC-5717 L08 Mon-Thu-23:00-00:00 NULL 322530 322530
LIC-5717 L08 Fri-Sat-23:00-02:00 322531 322530 322531
LIC-5816 M08 Mon-Sat-23:00-00:00 NULL 322526 322526
LIC-5816 M08 Sun-23:00-23:30 322527 322526 322527
LIC-5816 M08 Good Fri-23:00-23:30 322527 322526 322534
LIC-5816 M08 Christmas-23:00-23:30 322527 322526 322535

And these are the statements:
1.

update TABLE
   set max_ptr = (select min(ID) from TABLE t2
   where TABLE.Lic = t2.Lic and TABLE.Code = t2.Code
   and TABLE.ID != min_ptr and t2.ID != min_ptr
   group by Lic, Code)

update TABLE set TextData = TextData + '|' +
   (select t2.TextData from TABLE t2 where t2 ID = t2.max_ptr
    and t2.Lic = TABLE.Lic and t2.Code = TABLE.Code)
   where ID = min_ptr
   and Lic+Code in (select Lic+Code from TABLE t3
   where t3.ID != t3.max_ptr)

delete from TABLE where ID = max_ptr and ID != min_ptr

So, after running the 3 through once I get the following in the table:
Lic Code TextData max_ptr min_ptr ID
LIC-5717 L08 Mon-Thu-23:00-00:00 NULL 322530 322530
LIC-5816 M08 Mon-Sat-23:00-00:00|Sun-23:0 NULL 322526 322526
LIC-5816 M08 Good Fri-23:00-23:30 322527 322526 322534
LIC-5816 M08 Christmas-23:00-23:30 322527 322526 322535

It has deleted the 2nd line for LIC-5717 as expected but hasn't added the TextData to the first entry.

Looking at the code for statement 2 I can see that the last part of the statement is where the problem lies but I'm at a loss on how to put it right.

I've been left various scripts to run and I thought they were tried and tested as they've been used before. I can't contact the writer and my other goto person is on holiday.

Can anyone suggest a method of adapting the statement so that I can get on with my life? I'd be very grateful.

thanks.

Liza-Jane
Starting Member

3 Posts

Posted - 2013-09-02 : 10:10:52

Since adding this question I've been trying all sorts of different methods to get what I want. The nearest I've come is using some code I found on another forum which nearly does what I want. I'm so close ... and yet so far.

I tried this:

select Lic, Code, min_ptr,
(select cast(Textdata + '|' as varchar(max))
from TABLE
where (min_ptr = TABLE.min_ptr
and Lic+Code = TABLE.Lic+Code
for XML path('')
) as TextResults
from TABLE
where min_ptr = TABLE.min_ptr
group by LIC, min_ptr, code

I get back the rows I'm expecting but each of them have all of the textdata information on - not just the data relevant to their own ids.

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 10:21:51

just a small modification


select Lic, Code, min_ptr,
(select cast(Textdata + '|' as varchar(max))
from TABLE
where min_ptr = t.min_ptr
and Lic+Code = t.Lic+Code
for XML path('')
) as TextResults
from (SELECT DISTINCT Lic, Code, min_ptr FROM TABLE)t
where min_ptr = TABLE.min_ptr
group by LIC, min_ptr, code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Liza-Jane
Starting Member

3 Posts

Posted - 2013-09-02 : 10:26:38

Whoo! I think I've cracked it!

I adjusted the last code I used to add table definers (if that's the correct term) and I removed the last 'WHERE' and hey presto! I got the lines I required with the data I required.

select Lic, Code, min_ptr,
(select cast(Textdata + '|' as varchar(max))
from TABLE t1
where (t1.min_ptr = t2.min_ptr
and t1.Lic+Code = t2.Lic+Code
for XML path('')
) as TextResults
from TABLE t2
group by LIC, min_ptr, code

thanks v much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources