asked
need output in a particular format
hi all i have these following sample tables Table A Account_num Fname Lname date 111 aa bb 123 111 aa bb 123 112 cc dd 456 113 ee ff 789 111 aa bb 123 Table B Account_num Amount _paid quantity unitcost 111 100 222 11 111 200 111 22 112 400 444 33 113 500 333 44 For every distinct record in “table a” I need to print all the row values for that acct followed by all the row values from “table b” into a text file. Sample output :- (all these things shd b in same txt file ) 111aabb123(TABLE A acct 111 ) 11110022211(table b TRANSACTION for acct 111) 11120011122(table b TRANSACTION For acct 111) 112ccdd456 (TABLE A acct 112 ) 11240044433(table b TRANSACTION For acct 112) 113eeff789(TABLE A acct 113) 11350033344(table b TRANSACTION For acct 112) kindly ignore the space two between the lines. I tried my best to obtain this format but couldn achieve the desired result .... thanks in advance
SELECT DISTINCT CAST(account_num as varchar(3)) + fname + lname + date + ( select CHAR(10) + CAST(account_num as varchar(3)) + CAST(amount_paid as varchar(3)) + CAST(quantity as varchar(3)) + CAST(unitcost as varchar(3)) FROM TableB b WHERE b.Account_Num = a.Account_Num FOR XML PATH('')) FROM TableA a or like this if you want it as one single row with all the values: select DISTINCT char(10) + CAST(account_num as varchar(3)) + fname + lname + date + ( select CHAR(10) + CAST(account_num as varchar(3)) + CAST(amount_paid as varchar(3)) + CAST(quantity as varchar(3)) + CAST(unitcost as varchar(3)) FROM TableB b WHERE b.Account_Num = a.Account_Num FOR XML PATH('')) FROM TableA a FOR XML PATH('') After Olegs comment about CRLF vs LF, here's a third suggestion. It will return the whole thing as a string. SELECT STUFF(REPLACE((SELECT DISTINCT char(10) + CAST(account_num as varchar(3)) + fname + lname + date + ( SELECT CHAR(10) + CAST(account_num as varchar(3)) + CAST(amount_paid as varchar(3)) + CAST(quantity as varchar(3)) + CAST(unitcost as varchar(3)) FROM TableB b WHERE b.Account_Num = a.Account_Num FOR XML PATH('')) FROM TableA a FOR XML PATH('')),CHAR(10), CHAR(13) + CHAR(10)),1,2,'') Note that third argument for STUFF is 2, since the string starts with CRLF, which is two characters.
This is a very good answer. The only small problem is that char(10) is used to delimit the transactions which become a part of a parent account record. The results will be looking good in SSMS, but pasting them to Notepad will render them invalid because the true line break in Windows must consist of char(13) (carriage return) + char(10) (line feed). While advanced editors including SSMS do handle invalid line breaks seemlessly, it does become a problem in real world scenarios where the line breaks are CR + LF and not just LF. This means that the query in the answer must be tweaked first and adding char(13) to the left of char(10) in the sub-select is not possible due to xml entitizing of the CR. Something like this could be used to address this small issue:
SELECT
DISTINCT CAST(account_num as varchar(3)) +
fname + lname + date +
replace((
select CHAR(10) +
CAST(account_num as varchar(3)) +
CAST(amount_paid as varchar(3)) +
CAST(quantity as varchar(3)) +
CAST(unitcost as varchar(3))
FROM TableB b WHERE b.Account_Num = a.Account_Num
FOR XML PATH('')), char(10), char(13) + char(10))
FROM TableA a
@Magnus Ahlkvist Same here. I had to work on the huge mess created by one of the data conversion processes earlier this year when the work instructions for many, many records (originally nicely formatted) became a single blob delimited by the funny-looking squares instead of line breaks on the front screens courtesy of xml used by the conversion. It is very lovely how it works: if you have data with line breaks and use node.value then carriage returns simply disappear, but if you try forcing char(13) + char(10) + some_xml then they are entitized (become \ ). I never figured it out why there is so much trouble created around the line breaks, it should be simple, but it is not, every vendor likes own flavour (CRLF in Windows, LF in Unix etc). In a mean time, visualizing an old typewriter strongly suggests that CRLF is the only way to do it right, i.e the secretary grabs the handle and moves it to her left (carriage return) and then the carriage automatically drops down (line feed). Just because the **white out** was replaced with the fancy **backspace** key on the keyboard should not really mean that the CRLF should become something else :)
Most likely the problem with invalid cast is caused by your date column. Magnus already placed valid casts on all others, so add it to the date as well. Also, change the sizes to whatever is appropriate in your case (3 is used for the size because this is how the sample data was presented in the question). Add isnull to avoid returning nulls, for example, instead of having ... + FName + ... use ... + isnull(FName, '') + ... etc. And finally, if you need to have the output file as fixed length then consider casting the data to appropriately sized CHAR instead of VARCHAR. This is to allow all transaction rows to be of the same width and all of the account rows to be of the same width. For example, in you sample data all Amount_paid lists 3 digits amounts, but what about if one of the values is less than 100 bucks? Casting it to char of appropriate size will align your output. If you need the monetary values to be right aligned instead, use str function. For example,
select
cast(replace(str(100, 3), ' ', '0') as char(3)),
cast(replace(str(50, 3), ' ', '0') as char(3)); returns **100** and **050**, same width, left padded with zeroes.
this is the query --> SELECT STUFF(REPLACE((SELECT DISTINCT convert(varchar(1),RecordType)+convert(varchar(3),BrokerDealNumber)+convert(varchar(3),BranchNumber)+convert(varchar(3),InvestProfNum)+convert(varchar(9),PershAcctNumber)+convert(varchar(1),PershingAccontType) +convert(varchar(9),CUSIP)+convert(varchar(6),TransactionBookKeepingDate)+convert(varchar(18),Optionalfield1)+ ( SELECT convert(varchar(1),RecordType)+convert(varchar(3),BrokerDealNumber)+convert(varchar(3),BranchNumber)+convert(varchar(3),InvestProfNum)+convert(varchar(9),PershAcctNumber)+convert(varchar(1),PershingAccontType) +convert(varchar(9),CUSIP)+convert(varchar(6),TransactionBookKeepingDate)+convert(varchar(18),Optionalfield1)+ convert(varchar(1),SelectionUpdateType)+convert(varchar(46),Spacesatend)+convert(varchar(1),DisposalMethodIndicator)+convert(varchar(2),OverrideDisposalMethodSel) FROM dbo.TemptablepershingtableT ttab WHERE ttab.PershAcctNumber = atab.PershAcctNumber FOR XML PATH('')) FROM dbo.TemptablepershingtableA atab FOR XML PATH('')),CHAR(10), CHAR(13) + CHAR(10)),1,2,'')
Yes, it's wonderful. And often it's not consistent either. For example **123 + 'onetwothree'** will throw an error. But ** set @integerVariable='' ** will work. It doesn't make sense to me. I'd much rather have SQL Server consistently throw errors when I try to assign values of "wrong" datatype.
hi Magnus and Oleg ....HAppy new year guys ... I need your help again in this same issue .... Table A Account_num Fname Lname date 111 aa bb 123 111 aa bb 123 112 cc dd 456 113 ee ff 789 111 aa bb 123 Table B Account_num Amount _paid quantity unitcost 111 100 222 11 111 200 111 22 112 400 444 33 113 500 333 44 i am getting the following output 111aabb123 (acct num frm table a) 11110022211 (values frm table b for tht act num ) 11120011122 (values frm table b for tht act num ) 111aabb123 (again the same account number from A and then.. 11110022211 (values frm table b for tht act num ) 11120011122 (values frm table b for tht act num ) 112ccdd456 11240044433 113eeff789 11350033344 in above output lines..... line number 4, 5 and 6 are duplicate and are not required ... i want to have the distinct acct from table a and then all the records for that acct num from table b and then next different acct from table A and then all the records from table b and SO ON ..... thanks in advance
What query are you using? I'm using DISTINCT in my suggestion and I think I did test it with duplicate rows for TableA.
I have for TableA: Account_Num fname lname date ----------- ----- ----- ---- 111 aa bb 123 111 aa bb 123 112 aa dd 456 113 aa ff 789 TableB Account_Num Amount_paid quantity unitcost ----------- ----------- ----------- ----------- 111 100 222 11 111 200 111 22 112 400 444 33 113 500 333 44 And the query SELECT STUFF(REPLACE((SELECT DISTINCT char(10) + CAST(account_num as varchar(3)) + fname + lname + date + ( SELECT CHAR(10) + CAST(account_num as varchar(3)) + CAST(amount_paid as varchar(3)) + CAST(quantity as varchar(3)) + CAST(unitcost as varchar(3)) FROM TableB b WHERE b.Account_Num = a.Account_Num FOR XML PATH('')) FROM TableA a order by 1 FOR XML PATH('')),CHAR(10), CHAR(13) + CHAR(10)),1,2,'') It gives the result 111aabb123 11110022211 11120011122 112aadd456 11240044433 113aaff789 11350033344
I understand what you mean. Are all the columns you include from your TableA identical? Cause if you have for exampel Account_Num=111 and FName='aa' in one row, and Account_Num=111 and FName='bb' in another row, they are two different looking rows and both will be returned. If that's the case, you might want to do: SELECT STUFF(REPLACE((SELECT DISTINCT char(10) + CAST(account_num as varchar(3)) + (select TOP 1 fname + lname + date from TableA aa WHERE a.Account_Num = aa.Account_Num) + ( SELECT CHAR(10) + CAST(account_num as varchar(3)) + CAST(amount_paid as varchar(3)) + CAST(quantity as varchar(3)) + CAST(unitcost as varchar(3)) FROM TableB b WHERE b.Account_Num = a.Account_Num FOR XML PATH('')) FROM TableA a order by 1 FOR XML PATH('')),CHAR(10), CHAR(13) + CHAR(10)),1,2,'')
1 Person is following this question.
