Griouping records into new table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Griouping records into new table?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-28 : 11:31:47

I need to run through an existing table, grouping records together with the same mortgage number (ordered by sequence number) and add the notes columns into one column. This new amalgamated record is then added to a new table.
so:

existing_table
--------------

mortgage_number sequence_number notes_1 notes_2 notes_3 notes_4
100 1 Called today at 3:10pm, - -
102 1 Received payment of 1000.00 - -
100 2 was told that I should call back - -

new_table
---------

mortgage_number notes
100 Called today at 3:10pm, was told that I should call back
102 Received payment of 1000.00

How can I do this please? I am using MS SQL Server 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 13:41:07

try this:-

INSERT INTO NewTable
SELECT mortgage_number,LEFT(nl.noteslist,LEN(noteslist)-1) AS notes
FROM YourTable t
CROSS APPLY (SELECT notes_1 + ','+notes_2+ ','+notes_3 + ','+notes_4+',' AS [text()]
FROM YourTable
WHERE mortage_number=t.mortgage_number
FOR XML PATH(''))nl(noteslist)

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-29 : 11:51:00

Thanks visakh16. I modified the script to:


INSERT INTO new_table
SELECT mortgage_number,LEFT(nl.noteslist,LEN(noteslist)-1) AS notes
FROM old_table t
CROSS APPLY (SELECT notes_1 + ' '+ notes_2 + ' '+ notes_3 + ' ' + notes_4 + ' ' AS [text()]
FROM old_table
WHERE mortgage_number=t.mortgage_number
FOR XML PATH(''))nl(noteslist)

but I get an error: 'Invalid length parameter passed to the substring function'

Can anyone see where I have gone wrong please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 12:32:52

Change like this & try:-

INSERT INTO new_table
SELECT mortgage_number,LEFT(nl.noteslist,CASE WHEN LEN(nl.noteslist)=0 THEN 1 ELSE LEN(nl.noteslist) END -1) AS notes
FROM old_table t
CROSS APPLY (SELECT ISNULL(notes_1,'') + ' '+ ISNULL(notes_2,'') + ' '+ ISNULL(notes_3,'') + ' ' + ISNULL(notes_4,'') + ' ' AS [text()]
FROM old_table
WHERE mortgage_number=t.mortgage_number
FOR XML PATH(''))nl(noteslist)

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-29 : 13:10:01

MANY thanks for that - it has helped me a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 13:23:06

Did it work for you?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-29 : 16:18:50

Yes, thank you, it worked fine. I changed your code to:
INSERT INTO new_table
SELECT distinct mortgage_number,LEFT(nl.noteslist,CASE WHEN LEN(nl.noteslist)= 0 THEN 1 ELSE LEN(nl.noteslist) END ) AS notes
FROM old_table t
CROSS APPLY (SELECT ISNULL(notes_1,'') + ' '+ ISNULL(notes_2,'') + ' '+ ISNULL(notes_3,'') + ' ' + ISNULL(notes_4,'') + ' ' AS [text()]
FROM old_table
WHERE mortgage_number=t.mortgage_number ORDER BY sequence_number
FOR XML PATH(''))nl(noteslist)

and it did exactly what I needed.

The use of CROSS APPLY educated me, as I was ignorant of this.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 02:59:50

You are welcome

. Glad that i could help you out.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-30 : 05:53:47

Madhivanan once rightly said.Vishak is a master of Cross Apply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 08:36:08

quote:
Originally posted by ayamas

Madhivanan once rightly said.Vishak is a master of Cross Apply.

Is it?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-30 : 09:38:17

quote:
Originally posted by visakh16

quote:
Originally posted by ayamas

Madhivanan once rightly said.Vishak is a master of Cross Apply.

Is it?

Yup.Check this one out.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98536

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-30 : 09:53:58

I've found my requirement to be a little more complex than first thought.

My current table is:



old_table
---------

Converted_Mortgage_Number   Converted_Advance_Number   Original_Mortgage_Account_Number   Location_Code   Original_Mortgage_Account_Number_1   Activity_Date   Activity_Time   Sequence_Number   History_Text

100   1   100   109090   100   2007-06-27    84149   1   Sent to addresses 
100   1   100   109090   100   2007-07-17    131504   1   answermachine but I could not guarantee our customer is listed
100   1   100   109090   100   2007-07-17    131504   2   I did not leave a message
100   1   100   109090   100   2007-07-17    131619   1   NOTHING LISTED.
100   1   100   109090   100   2007-07-17    131647   1   Mini Review *****
100   1   100   109090   100   2007-07-17    131647   2   Who chasing ? - John Doe
100   1   100   109090   100   2007-07-17    131647   3   Payments being received ? NO
100   1   100   109090   100   2007-07-17    131647   4   Is on screen balance correct ? YES
100   1   100   109090   100   2007-07-17    131647   5   Current Status? - MR BANKRUPT. MRS WAS PAYING,I WILL SEND
100   1   100   109090   100   2007-07-17    131647   6   A INSOURCE LETTER. I CALLED DIR ENQS AND NOTHING WAS LISTED AND I CALLED THE H
100   1   100   109090   100   2007-07-17    131647   7   OME NUMBER AND DID NOT LEAVE A MESSAGE. AWAIT RESPONSE TO THE LETTER.
100   1   100   109090   100   2007-07-17    131943   1   Sent to addresses 
100   1   100   109090   100   2007-07-31    193709   1   lmoam
100   1   100   109090   100   2007-07-31    193728   1   Sent to addresses
100   1   100   109090   100   2007-08-23    233247   1   Account sold
100   1   100   109090   100   2007-08-28    233427   1   Account sold

[code]

I have created a new (slightly different table)for the INSERT INTO and want the new table to be:

[code]

Converted_Mortgage_Number  Activity_Date   Activity_Time   Sequence_Number   History_Text

100      2007-06-27    84149   1   Sent to addresses 
100      2007-07-17    131504   1   answermachine but I could not guarantee our customer is listed I did not leave a message
100      2007-07-17    131619   1   NOTHING LISTED.
100      2007-07-17    131647   1   Mini Review ***** Who chasing ? - John Doe Payments being received ? NO Is on screen balance correct ? YES Current Status? - MR BANKRUPT. MRS WAS PAYING,I WILL SEND A INSOURCE LETTER. I CALLED DIR ENQS AND NOTHING WAS LISTED AND I CALLED THE H OME NUMBER AND DID NOT LEAVE A MESSAGE. AWAIT RESPONSE TO THE LETTER .
100      2007-07-17    131943   1   Sent to addresses 
100      2007-07-31    193709   1   lmoam
100      2007-07-31    193728   1   Sent to addresses
100      2007-08-23    233247   1   Account sold
100      2007-08-28    233427   1   Account sold

after the query is complete.

How can I reapply the previous sql query to achieve this please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 10:35:18

[code]INSERT INTO new_table
SELECT t.Converted_Mortgage_Number,t.Activity_Date,
t.Activity_Time,t.Sequence_Number,
LEFT(nl.noteslist,CASE WHEN LEN(nl.noteslist)= 0 THEN 1 ELSE LEN(nl.noteslist) END ) AS History_Text
FROM (SELECT Converted_Mortgage_Number,Activity_Date,Activity_Time, Sequence_Number
FROM old_table
WHERE Sequence_Number=1)t
CROSS APPLY (SELECT ISNULL(History_Text,'') + ' ' AS [text()]
FROM old_table
WHERE Activity_Time=t.Activity_Time
ORDER BY sequence_number
FOR XML PATH(''))nl(noteslist)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 10:46:42

quote:
Originally posted by ayamas

quote:
Originally posted by visakh16

quote:
Originally posted by ayamas

Madhivanan once rightly said.Vishak is a master of Cross Apply.

Is it?

Yup.Check this one out.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98536

I'm honoured

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-30 : 15:16:25

quote:

Originally posted by visakh16

INSERT INTO new_table
SELECT t.Converted_Mortgage_Number,t.Activity_Date,
t.Activity_Time,t.Sequence_Number,
LEFT(nl.noteslist,CASE WHEN LEN(nl.noteslist)= 0 THEN 1 ELSE LEN(nl.noteslist) END ) AS History_Text
FROM (SELECT Converted_Mortgage_Number,Activity_Date,Activity_Time,   Sequence_Number 
FROM old_table 
WHERE Sequence_Number=1)t
CROSS APPLY (SELECT ISNULL(History_Text,'') + ' ' AS [text()]
FROM old_table
WHERE Activity_Time=t.Activity_Time 
ORDER BY sequence_number
FOR XML PATH(''))nl(noteslist)

I get a 'column name or number of supplied values does not match table definition' error. This is strange because my new table us merely a truncated copy of my old table, but with the table name changed.

Any ideas guys? I just can't see why this should be.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-31 : 08:40:11

OK, I finally figured it out.

The INSERT INTO works if the new table structure only has the columns

Converted_Mortgage_Number
Activity_Date
Activity_Time
Sequence_Number
History_Text

but what if my new table had additional columns like:

Converted_Mortgage_Number
Converted_Advance_Number
Location_Code
Activity_Date
Activity_Time
Sequence_Number
History_Text

How should the query by rewritten to allow for this please?

Sorry for being dumb

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-02 : 13:13:14

The query, supplied very kindly be Visakh16:


INSERT INTO new_table
SELECT t.Converted_Mortgage_Number,t.Activity_Date,
t.Activity_Time,t.Sequence_Number,
LEFT(nl.noteslist,CASE WHEN LEN(nl.noteslist)= 0 THEN 1 ELSE LEN(nl.noteslist) END ) AS History_Text
FROM (SELECT Converted_Mortgage_Number,Activity_Date,Activity_Time,   Sequence_Number 
FROM old_table 
WHERE Sequence_Number=1)t
CROSS APPLY (SELECT ISNULL(History_Text,'') + ' ' AS [text()]
FROM old_table
WHERE Activity_Time=t.Activity_Time 
ORDER BY sequence_number
FOR XML PATH(''))nl(noteslist)

worked fine with my sample data. However this data had all the same
Converted_Mortgage_Number. Now I've come to use it with a large database (several million records)I find that after working well with the first Converted_Mortgage_Number (row 1), all subsequent records are all wrong.

Is there a way to use a cursor to step through the records and perform the required concantenation please? Maybe it's too big a requirement?
Anyway, it's beyond my curremt level.

Anyone please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources