August 18, 2016 at 5:39 am
Good Afternoon All
I have a project of data migration from old system to new system. The new system needs a flat pipe file in a specific format.
On my Ben table, I have an ID that could have multiple records, lets say ten records. This is a table used to store a clients beneficiaries of mobile numbers. 1 client can have many beneficiaries with different mobile numbers.
I want to select this data into a single ID with all the records per column. Example as per below.
BEN
ID |NUM| Amount|Cur|date|
T01 | 123 | 50.00 | NAD | 20160818|
T01 | 124 | 20.00 | NAD | 20160817|
T02 | 125 | 30.00 | NAD | 20160816|
T02 | 126 | 10.00 | NAD | 20160815|
T03 | 127 | 40.00 | NAD | 20160814|
Desired output
ID | NUM | Amount |Cur |date |
T01 | 123::124 | 50.00::20.00 | NAD::NAD | 20160818::20160817|
T02 | 125::126 | 30.00::10.00 | NAD::NAD | 20160816::20160815|
T03 | 127 | 40.00 | NAD | 20160814|
The current query I have is this and is not giving what i want.
Select Main.ID,
Left(Main.BEN,Len(Main.BEN)-1) As "Data"
From
(
Select distinct ST2.ID,
(
Select ST1.mobile_number + '::' AS [text()],
ST1.amount + '::' AS [text()]
From MTCBEN ST1
Where ST1.ID = ST2.ID
ORDER BY ST1.ID
For XML PATH ('')
) [BEN]
From MTCBEN ST2
) [Main]
Thank you so much for your help guys.. This will save my life.
Regards
August 18, 2016 at 5:57 am
shani19831 (8/18/2016)
Good Afternoon AllI have a project of data migration from old system to new system. The new system needs a flat pipe file in a specific format.
On my Ben table, I have an ID that could have multiple records, lets say ten records. This is a table used to store a clients beneficiaries of mobile numbers. 1 client can have many beneficiaries with different mobile numbers.
I want to select this data into a single ID with all the records per column. Example as per below.
BEN
ID |NUM| Amount|Cur|date|
T01 | 123 | 50.00 | NAD | 20160818|
T01 | 124 | 20.00 | NAD | 20160817|
T02 | 125 | 30.00 | NAD | 20160816|
T02 | 126 | 10.00 | NAD | 20160815|
T03 | 127 | 40.00 | NAD | 20160814|
Desired output
ID | NUM | Amount |Cur |date |
T01 | 123::124 | 50.00::20.00 | NAD::NAD | 20160818::20160817|
T02 | 125::126 | 30.00::10.00 | NAD::NAD | 20160816::20160815|
T03 | 127 | 40.00 | NAD | 20160814|
The current query I have is this and is not giving what i want.
Select Main.ID,
Left(Main.BEN,Len(Main.BEN)-1) As "Data"
From
(
Select distinct ST2.ID,
(
Select ST1.mobile_number + '::' AS [text()],
ST1.amount + '::' AS [text()]
From MTCBEN ST1
Where ST1.ID = ST2.ID
ORDER BY ST1.ID
For XML PATH ('')
) [BEN]
From MTCBEN ST2
) [Main]
Thank you so much for your help guys.. This will save my life.
Regards
Please provide sample DDL and INSERT statements, along with expected results, so that people can run your query (or a test version of it, which demonstrates the problem) for themselves. The link in my signature shows how to do this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 18, 2016 at 6:02 am
CREATE TABLE [dbo].[MTCBEN](
[ID] [varchar](250) NULL,
[MOBILE_NUMBER] [varchar](50) NULL,
[AMOUNT] [varchar](50) NULL,
[CURRENCY] [varchar](50) NULL,
[PROCESSING_DATE] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[MTCBEN]
([ID]
,[MOBILE_NUMBER]
,[AMOUNT]
,[CURRENCY]
,[PROCESSING_DATE])
VALUES
(
T100 ,0812365066,100,NAD,20160810
T100 ,0812365067,10,NAD,20160811
T100 ,0812365069,150,NAD,20160812
T101 ,0812365060,20,NAD,20160813
T101 ,0812365065,30,NAD,20160814
T102 ,0812365064,150,NAD,20160815
)
GO
Expected results
T100 |0812365066::0812365067::0812365069|100::10::150|NAD::NAD::NAD|20160810::20160811::20160812|
T101 |0812365060::0812365065|20::30|NAD::NAD::NAD|20160813::20160814|
T102 |0812365064|150|NAD|20160815|
August 18, 2016 at 9:03 am
Sorry guys, dedication and eager to learn has forced me to write this query is retrieving the information that I want.
select ID,
stuff((SELECT ':' + cast(mobile_number as varchar(10))
FROM MTCBEN t2
where t2.ID = t1.ID
FOR XML PATH('')),1,1,'') as mobile,
stuff((SELECT ':' + cast(amount as varchar(10))
FROM MTCBEN t3
where t3.ID = t1.ID
FOR XML PATH('')),1,1,'') as amount,
stuff((SELECT ':' + cast(currency as varchar(10))
FROM MTCBEN t4
where t4.ID = t1.ID
FOR XML PATH('')),1,1,'') as currency,
stuff((SELECT ':' + cast(processing_date as varchar(10))
FROM MTCBEN t5
where t5.ID = t1.ID
FOR XML PATH('')),1,1,'') as 'Date'
from MTCBEN t1
group by ID
August 18, 2016 at 9:34 am
Well done on sorting that out.
Note that the original script you provided contains several errors. Here is one that works, using a temp table and with some guesswork applied to improve the data types:
IF OBJECT_ID('tempdb..#MTCBEN', 'U') IS NOT NULL
DROP TABLE #MTCBEN;
CREATE TABLE #MTCBEN
(
ID VARCHAR(50) NULL
,MOBILE_NUMBER VARCHAR(50) NULL
,AMOUNT DECIMAL(18, 6) NULL
,CURRENCY CHAR(3) NULL
,PROCESSING_DATE DATE
);
INSERT #MTCBEN
(ID, MOBILE_NUMBER, AMOUNT, CURRENCY, PROCESSING_DATE)
VALUES ('T100', '0812365066', 100, 'NAD', '20160810'),
('T100', '0812365067', 10, 'NAD', '20160811'),
('T100', '0812365069', 150, 'NAD', '20160812'),
('T101', '0812365060', 20, 'NAD', '20160813'),
('T101', '0812365065', 30, 'NAD', '20160814'),
('T102', '0812365064', 150, 'NAD', '20160815');
SELECT *
FROM #MTCBEN m;
SELECT t1.ID
, STUFF((SELECT ':' + CAST(t2.MOBILE_NUMBER AS VARCHAR(10))
FROM #MTCBEN t2
WHERE t2.ID = t1.ID
FOR
XML PATH('')
), 1, 1, '') AS mobile
, STUFF((SELECT ':' + CAST(t3.AMOUNT AS VARCHAR(10))
FROM #MTCBEN t3
WHERE t3.ID = t1.ID
FOR
XML PATH('')
), 1, 1, '') AS amount
, STUFF((SELECT ':' + CAST(t4.CURRENCY AS VARCHAR(10))
FROM #MTCBEN t4
WHERE t4.ID = t1.ID
FOR
XML PATH('')
), 1, 1, '') AS currency
, STUFF((SELECT ':' + CAST(t5.PROCESSING_DATE AS VARCHAR(10))
FROM #MTCBEN t5
WHERE t5.ID = t1.ID
FOR
XML PATH('')
), 1, 1, '') AS 'Date'
FROM #MTCBEN t1
GROUP BY t1.ID;
Note also that the results generated by your query (which look fine) do not quite match the expected results you mentioned.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 18, 2016 at 9:46 am
Thank you for the reply.
Yes the result is not really the same but i can work from excel to change it. something I would love to avoid..
I cant get the :: correct, as it appends : at the beginning of the record and i don't know how to remove it.
August 18, 2016 at 10:00 am
shani19831 (8/18/2016)
Thank you for the reply.Yes the result is not really the same but i can work from excel to change it. something I would love to avoid..
I cant get the :: correct, as it appends : at the beginning of the record and i don't know how to remove it.
Try this
SELECT t1.ID
, STUFF((SELECT '::' + CAST(t2.MOBILE_NUMBER AS VARCHAR(10))
FROM #MTCBEN t2
WHERE t2.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS mobile
, STUFF((SELECT '::' + CAST(t3.AMOUNT AS VARCHAR(10))
FROM #MTCBEN t3
WHERE t3.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS amount
, STUFF((SELECT '::' + CAST(t4.CURRENCY AS VARCHAR(10))
FROM #MTCBEN t4
WHERE t4.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS currency
, STUFF((SELECT '::' + CAST(t5.PROCESSING_DATE AS VARCHAR(10))
FROM #MTCBEN t5
WHERE t5.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS 'Date'
FROM #MTCBEN t1
GROUP BY t1.ID;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 18, 2016 at 10:10 am
Thank you so much this is correct. You saved me hours from excel.
Phil Parkin (8/18/2016)
shani19831 (8/18/2016)
Thank you for the reply.Yes the result is not really the same but i can work from excel to change it. something I would love to avoid..
I cant get the :: correct, as it appends : at the beginning of the record and i don't know how to remove it.
Try this
SELECT t1.ID, STUFF((SELECT '::' + CAST(t2.MOBILE_NUMBER AS VARCHAR(10))
FROM #MTCBEN t2
WHERE t2.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS mobile
, STUFF((SELECT '::' + CAST(t3.AMOUNT AS VARCHAR(10))
FROM #MTCBEN t3
WHERE t3.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS amount
, STUFF((SELECT '::' + CAST(t4.CURRENCY AS VARCHAR(10))
FROM #MTCBEN t4
WHERE t4.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS currency
, STUFF((SELECT '::' + CAST(t5.PROCESSING_DATE AS VARCHAR(10))
FROM #MTCBEN t5
WHERE t5.ID = t1.ID
FOR
XML PATH('')
), 1, 2, '') AS 'Date'
FROM #MTCBEN t1
GROUP BY t1.ID;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply