February 4, 2015 at 11:56 am
[font="Arial"]I am wondering how I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together, I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.[/font]
00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000#19NEVER BEEN THERE - HAD A TEST RADIO W/DI2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000(19the account. 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000%19THIS RADIO DID NOT EXIST. Emailed to get2015-01-19 15:34:59.000
00000000332015-01-16 18:04:09.00000"19AS DEACTIVATED TODAY, CONFRMED 2015-01-19 18:42:48.000
00000000332015-01-16 18:04:09.00000$19D. 2015-01-19 18:42:48.000
00000000332015-01-16 18:04:09.00000!19DELETED BILLING LINE FOR ACCT 185044 - W2015-01-19 18:42:48.000
00000000332015-01-16 18:04:09.00000#19WITH Carl P THAT THIS RADIO NEVER EXISTE2015-01-19 18:42:48.000[/size]
February 4, 2015 at 12:58 pm
Please provide create table statements, insert statements and sample expected data so we can help you. Assuming you have a UNIQUE key for each row between the two systems this should be just a simple join on said key and enumerate the fields you need individually.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2015 at 1:02 pm
Can you provide an example?
February 4, 2015 at 1:15 pm
February 4, 2015 at 1:53 pm
I did a search and found similar using xml path()..Thank You! Now I have to figure out how to incorporate in sp_executesql, because I am joining monthly tables and they are set to a variable because each month the change.
February 4, 2015 at 2:28 pm
So I get an error that is pointing to the semicolon, but when I remove it, I get an error stating that Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
;WITH ARNOTES as ( select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1502]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1501]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1412]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1411]) ;WITH act_notes as ( SELECT
DISTINCT SS.AR_NUMBER,
SS.HD_DATE_TIMEX,
SS.TRANS_NO,
(SELECT
' ' + RTRIM(LTRIM(Message_2 ))
FROM
arlsq01.DSG.dbo.DICE_COTRMS1501 US
WHERE
US.AR_NUMBER = SS.AR_NUMBER
and US.HD_DATE_TIMEX = SS.HD_DATE_TIMEX FOR XML PATH('')) [ACCT/NOTES]
FROM
arlsq01.DSG.dbo.DICE_COTRMS1501 SS
GROUP BY
SS.AR_NUMBER,
SS.HD_DATE_TIMEX,
SS.TRANS_NO ) select
*
from
act_notes
Inner join
ARNOTES
on act_notes.ar_number = ARNOTES.ar_number Select
ARNOTES.AR_NUMBER,
HD_DATE_TIMEX,
ARNOTES.TRANS_NO,
ARNOTES.MESSAGE_COUNTER,
DATEDIFF(day,
HD_DATE_TIMEX,
GETDATE()) as daysoldIND,
ARNOTES.Message_2,
ARNOTES.SQL_LAST_UPDATE
from
ARNOTES
ORDER By
ARNOTES.AR_NUMBER,
HD_DATE_TIMEX,
ARNOTES.TRANS_NO,
ARNOTES.Message_2,
ARNOTES.MESSAGE_COUNTER ,
ARNOTES.SQL_LAST_UPDATE
February 4, 2015 at 2:56 pm
when you have multiple CTEs for one query you don't repeat the WITH keyword, you put each in brackets and separate them with a comma
If you give us table definitions and data we can play along. In the meantime this should get you past the semicolon error. I havent checked for other syntax or logic issues though.
;WITH ARNOTES as ( select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1502]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1501]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1412]
UNION
ALL select
*
from
[arlsq01].[DSG].[dbo].[DICE_COTRMS1411]), act_notes as ( SELECT
DISTINCT SS.AR_NUMBER,
SS.HD_DATE_TIMEX,
SS.TRANS_NO,
(SELECT
' ' + RTRIM(LTRIM(Message_2 ))
FROM
arlsq01.DSG.dbo.DICE_COTRMS1501 US
WHERE
US.AR_NUMBER = SS.AR_NUMBER
and US.HD_DATE_TIMEX = SS.HD_DATE_TIMEX FOR XML PATH('')) [ACCT/NOTES]
FROM
arlsq01.DSG.dbo.DICE_COTRMS1501 SS
GROUP BY
SS.AR_NUMBER,
SS.HD_DATE_TIMEX,
SS.TRANS_NO ) select
*
from
act_notes
Inner join
ARNOTES
on act_notes.ar_number = ARNOTES.ar_number Select
ARNOTES.AR_NUMBER,
HD_DATE_TIMEX,
ARNOTES.TRANS_NO,
ARNOTES.MESSAGE_COUNTER,
DATEDIFF(day,
HD_DATE_TIMEX,
GETDATE()) as daysoldIND,
ARNOTES.Message_2,
ARNOTES.SQL_LAST_UPDATE
from
ARNOTES
ORDER By
ARNOTES.AR_NUMBER,
HD_DATE_TIMEX,
ARNOTES.TRANS_NO,
ARNOTES.Message_2,
ARNOTES.MESSAGE_COUNTER ,
ARNOTES.SQL_LAST_UPDATE
February 4, 2015 at 3:05 pm
Thanks, now just have to remove the dups.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply