Merging Row Data or Any Suggestions

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

Merging Row Data or Any Suggestions

Author

Topic

c4e4
Starting Member

16 Posts

Posted - 2008-02-05 : 16:43:41

Hi
I have been given a table that contains data in this format:

SALESPERSON SALESPERSON_ID CLIENT DATE_FROM DATE_TO AGE
TOM 12345 NULL NULL NULL NULL
NULL NULL MARYSMITH 1/1/2008 12/31/2008 46
NULL NULL JANEDOW 1/1/2008 12/31/2008 24
ED 56789 NULL NULL NULL NULL
NULL NULL TOMJONES 1/1/2008 12/31/2008 65
ANTHONY 243546 NULL NULL NULL NULL
NULL NULL BEVBLACK 1/1/2008 12/31/2008 15
NULL NULL JEANTHOMAS 1/1/2008 12/31/2008 29

Basically this is ONE table that contains header and detail data ordered sequentially. There are not unique identifiers for the rows. The rows are ordered sequentially so that each SALESPERSON is followed by one or more CLIENTs.

If I could merge the rows, the result would look like:

SALESPERSON SALESPERSON_ID CLIENT DATE_FROM DATE_TO AGE
TOM 12345 MARYSMITH 1/1/2008 12/31/2008 46
TOM 12345 JANEDOW 1/1/2008 12/31/2008 24
ED 56789 TOMJONES 1/1/2008 12/31/2008 65
ANTHONY 243546 BEVBLACK 1/1/2008 12/31/2008 15
ANTHONY 243546 JEANTHOMAS 1/1/2008 12/31/2008 29

I am not how to do this with this data.

I also thought maybe it would be better to add unique identifiers to each set of SALESPERSONs/CLIENTs, and work with the data that way, but I am not sure how to do that.

Any help or suggestions would be appreciated. I have no ability to change this data - I have to try to work with it if possible.

JLH

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 17:34:51

quote:
Originally posted by c4e4
I have been given a table that contains data in this format:

SALESPERSON SALESPERSON_ID CLIENT DATE_FROM DATE_TO AGE
TOM 12345 NULL NULL NULL NULL
NULL NULL MARYSMITH 1/1/2008 12/31/2008 46
NULL NULL JANEDOW 1/1/2008 12/31/2008 24
ED 56789 NULL NULL NULL NULL
NULL NULL TOMJONES 1/1/2008 12/31/2008 65
ANTHONY 243546 NULL NULL NULL NULL
NULL NULL BEVBLACK 1/1/2008 12/31/2008 15
NULL NULL JEANTHOMAS 1/1/2008 12/31/2008 29

Did you slap them?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-05 : 17:50:02

You actually cannot do this reliably if you cannot identify each row. Furthermore, your data is not ordered sequentially unless you have an ORDER BY. If not, it is coming out in some order determined by the database and any reliance on that order is likely to cause you trouble. If you cannot specify an ORDER BY then you can't practically solve this problem using sets. Any solution based on what you have told us so far will not work.
If you can add a link between the client & salesperson and you are in with a good chance simply by joining the table to itself.
A better design would be to have 2 tables.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 18:00:18

The following may work if the ordering is set in the file:


/* @a represents the file he is working with */
DECLARE @a TABLE ( SALESPERSON VARCHAR(15), SALESPERSON_ID INT, CLIENT VARCHAR(15), DATE_FROM DATETIME, DATE_TO DATETIME, AGE INT )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( 'TOM', 12345, NULL, NULL, NULL, NULL )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( NULL, NULL, 'MARYSMITH', '1/1/2008', '12/31/2008', 46 )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( NULL, NULL, 'JANEDOW', '1/1/2008', '12/31/2008', 24 )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( 'ED', 56789, NULL, NULL, NULL, NULL )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( NULL, NULL, 'TOMJONES', '1/1/2008', '12/31/2008', 65 )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( 'ANTHONY', 243546, NULL, NULL, NULL, NULL )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( NULL, NULL, 'BEVBLACK', '1/1/2008', '12/31/2008', 15 )
INSERT @a ( SALESPERSON, SALESPERSON_ID, CLIENT, DATE_FROM, DATE_TO, AGE )
VALUES ( NULL, NULL, 'JEANTHOMAS', '1/1/2008', '12/31/2008', 29 )

DECLARE @b TABLE
    (
      i INT IDENTITY,
      SALESPERSON VARCHAR(15),
      SALESPERSON_ID INT,
      CLIENT VARCHAR(15),
      DATE_FROM DATETIME,
      DATE_TO DATETIME,
      AGE INT
    )

INSERT  @b
        (
          SALESPERSON,
          SALESPERSON_ID,
          CLIENT,
          DATE_FROM,
          DATE_TO,
          AGE 
        )
        SELECT  *
        FROM    @a

SELECT  b.SALESPERSON,
        a.SALESPERSON_ID,
        b.CLIENT,
        b.DATE_FROM,
        b.DATE_TO,
        b.AGE
FROM    @b b
        JOIN ( SELECT   a.SALESPERSON_ID,
                        a.i AS FromI,
                        b.i AS ToI
               FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY i ) AS rn,
                                    i,
                                    SALESPERSON_ID
                          FROM      @b
                          WHERE     SALESPERSON_ID IS NOT NULL
                        ) a
                        JOIN ( SELECT   ROW_NUMBER() OVER ( ORDER BY i ) AS rn,
                                        i,
                                        SALESPERSON_ID
                               FROM     @b
                               WHERE    SALESPERSON_ID IS NOT NULL
                               UNION
                               SELECT   SUM(CASE WHEN SALESPERSON_ID IS NOT NULL
                                                 THEN 1
                                                 ELSE 0
                                            END) + 1,
                                        MAX(i),
                                        NULL
                               FROM     @b
                             ) b ON a.rn + 1 = b.rn
             ) a ON b.i BETWEEN a.FromI AND a.ToI

c4e4
Starting Member

16 Posts

Posted - 2008-02-05 : 20:55:35

Thanks for your responses. No I did not slap anyone...yet.
An hour or so ago, I had a friend write a shell script that modified the data to merge the rows (repeating the salesperson piece in each of the following client rows). Thanksfully the rows are in order, otherwise it would be chaos. I will have to see if the source of the data can add identifiers to the related records so they can be split into two tables so this is manageable without the shell script.
Cheers!

JLH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources