October 14, 2008 at 5:55 am
table1
acctID Ntext
1 hello
1 This
1 Is
2 notReal
2 What
3 Final
Table2
acctID field1 field2
1 f1data1 f2data1
2 f1data2 f2data2
3 f1data3 f2data3
I want to insert data into another table. I need to concatenate all fields from both tables to look like this:
Table3
acctID allCollsTable1and2
1 f1data1 f2data1 helloThisIs
2 f1data2 f2data2 notReal What
3 f1data3 f2data3 Final
Iseriously need help with a query like this without duplicating the ID column in the third table.
October 14, 2008 at 8:13 am
Try something like this:
acctID Ntext
1 hello
1 This
1 Is
2 notReal
2 What
3 Final
Table2
acctID field1 field2
1 f1data1 f2data1
2 f1data2 f2data2
3 f1data3 f2data3
create table #T1 (
AcctID int,
C1 varchar(100));
create table #T2 (
AcctID int,
F1 varchar(100),
F2 varchar(100));
insert into #T1 (AcctID, C1)
select 1, 'hello' union all
select 1, 'This' union all
select 1, 'Is' union all
select 2, 'notReal' union all
select 2, 'What' union all
select 3, 'Final';
insert into #T2 (AcctID, F1, F2)
select 1, 'f1data1', 'f2data1' union all
select 2, 'f1data2', 'f2data2' union all
select 3, 'f1data3', 'f2data3';
select AcctID, C1
into #T3
from #T1
union all
select AcctID, F1
from #T2
union all
select AcctID, F2
from #T2;
;with CTE2 as
(select distinct AcctID
from #T3)
select AcctID,
replace(
replace(
replace(
cast(
(select C1
from #T3
where acctid = cte2.acctid
for XML raw)
as varchar(max)),
'"/><row C1="', ' '),
'<row C1="', ''),
'"/>', '')
from cte2;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2008 at 8:36 am
GSquared,
The table with duplicate acctID can have as much as 20 or more Ntext (which is notes to the acct. So basically, an account can have several notes that's why the account numbers appear multiple times).
Does it mean I should have a select for each entry? That table has 1.5 million records. if i'm able to pull this off, I should have 15K records in all.
The code you sent is kind of complicated for me. What should i do?
October 14, 2008 at 9:11 am
Notes AcctID
P.D. CHEQUES RECEIVED 01-010153909
PD CHEQUES EXPIRED 01-010153909
POTENTIAL NIS 01-010153909
REMOVE FROM PREDICTIVE 01-010153909
CHQ IS GOOD TO POST 01-010694075
ABLE NOW LOCATED PLEASE C 01-010694075
ALL CONSUMER TO MAKE SURE 01-010694075
ATED 04/12/07 WAS UNLOCAT 01-010694075
CALL CONNECTED 01-010694075
------------------------- 01-011060614
AND CALL NOT GOING THROU 01-011060614
AND NO CONS THERE IS WRN 01-011060614
'S NAME & (ZACH'S) BUT NL 01-011060614
S NO TELE SO CN'T REP CON 01-011060614
See Purged Notes--------- 01-011060614
SHOWS CFI AT ONE TME CON 01-011060614
------------------------- 01-011360372
CALL CONNECTED 01-011360372
MANAGER REVIEW 01-011360372
NO ANSWER 01-011360372
POTENTIAL NIS 01-011360372
REMOVE FROM PREDICTIVE 01-011360372
Here is what this big table (1.5 million records) looks like. One account can have numerous notes. I'm required to concatenate all the notes for one account into one column and have the second column as the acctID with no duplicates.
All help/feedback will be appreciated.
October 14, 2008 at 9:11 am
As another option, you could create a function to concat the data by acctID, and then call that function in a select statement, like this:
Create FUNCTION dbo.Concat(@n AS INT) --@N is your acctID
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @Sql NVARCHAR(500)
SET @Sql = ''
SELECT @Sql = @Sql + ' ' + NText FROM dbo.Table1
WHERE acctID = @n
SELECT @Sql = @Sql + ' ' + field1 FROM dbo.Table2
WHERE acctID = @n
SELECT @Sql = @Sql + ' ' + field2 FROM dbo.Table2
WHERE acctID = @n
RETURN LTRIM(@Sql)
END
GO
SELECT DISTINCT acctID, dbo.Concat(acctID)
FROM
(SELECT DISTINCT acctID
FROM table1 UNION
SELECT DISTINCT acctID
FROM table2) a
October 14, 2008 at 9:16 am
Please take a look at the problematic table I just posted and see if you can make the code any simpler.
October 14, 2008 at 9:44 am
In the function I wrote, you'll need to change the input param away from int, and then rename the columns and tables to match your latest submission. You might also find that you need to bump up the length of the string that the function returns.
Otherwise, it shouldn't care how many values you need to concatenate.
To prove that to yourself, try
Declare @Sql NVarchar(500)
SELECT @Sql = @Sql + ' ' + Notes FROM YourTable
WHERE acctID = '01-010153909'
Print @Sql
October 14, 2008 at 10:20 am
Thank you a million times.
I modified the Function and
it works fine, the way it should.
I appreciate your help.
The last 2 weeks has been hell for me.
Thank you, thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply