question

AbhiD avatar image
AbhiD asked

How to achieve the grouping in this sql query???

With this query of mine i am able to group by atab.PershAcctNumber but i also want to do group by Cusip along with atab.PershAcctNumber. how to achieve this grouping? i apologize for putting the whole query but i was just confused abt from where to start. SELECT STUFF(REPLACE((SELECT DISTINCT char(10)+ convert(varchar(1),RecordType)+convert(varchar(3),BrokerDealNumber) +convert(varchar(3),BranchNumber)+replicate (' ',3) +(select top 1 convert(varchar(9),atab.PershAcctNumber) +convert(varchar(1),PershingAccontType)+convert(varchar(9),CUSIP) +convert(varchar(6),TransactionBookKeepingDate)+convert(varchar(18),Optionalfield1) +convert(varchar(1),'R')+convert(varchar(46),Spacesatend) +convert(varchar(8),SourceSystem)+convert(varchar(3),PershingAcctNaviType) +convert(varchar(2),Transactiontype)+convert(varchar(1),'C')+convert(varchar(54),TransMinorAndSRCSystem) +convert(varchar(8),TransactionInitiationDate)+ convert(varchar(8),TransactionStatusDate) +convert(varchar(8),TransactionTradeDate)+convert(varchar(8),TransactionSettleDate) +replicate(' ',20) +CONVERT(varchar(18), STUFF('000000000000000000', 19 - LEN(REPLACE(CAST(CAST(sumofquantity AS DECIMAL(18, 5)) AS VARCHAR(50)), '.', '')), LEN(REPLACE(CAST(CAST(sumofquantity AS DECIMAL(18, 5)) AS VARCHAR(50)), '.', '')), REPLACE(CAST(CAST(sumofquantity AS DECIMAL(18, 5)) AS VARCHAR(50)), '.', ''))) +convert(varchar(18),Transcost) +convert(varchar(470),Optionalfield4) from dbo.PershExtracttableA aa where atab.PershAcctNumber= aa.PershAcctNumber)+ ( SELECT char(10)+convert(varchar(1),RecordType)+convert(varchar(3),BrokerDealNumber) +convert(varchar(3),BranchNumber) +replicate(' ',3) +convert(varchar(9),PershAcctNumber) +convert(varchar(1),PershingAccontType)+convert(varchar(9),TTAB.CUSIP)+convert(varchar(6),TransactionBookKeepingDate) +convert(varchar(18),Optionalfield1)+convert(varchar(1),'R') +convert(varchar(46),Spacesatend)+convert(varchar(1),DisposalMethodIndicator) +convert(varchar(2),' ')+convert(varchar(20),DisposalMethodTxt)+convert(varchar(6),TAxlotentryDate) +convert(varchar(6),TaxlotBatNum)+convert(varchar(8),TaxlottradeDate)+ convert(varchar(4),Taxlotstattuscode)+convert(varchar(18),LotAvailableQuantity)+replicate(' ',18)+convert(varchar(18),TaxLotUnitCost) +replicate(' ',18)+replicate(' ',480) --+convert(varchar(480),Optionalfield4) FROM dbo.pershingextracttableT ttab WHERE ttab.PershAcctNumber = atab.PershAcctNumber FOR XML PATH('')) FROM dbo.PershExtracttableA atab FOR XML PATH('')),CHAR(10), CHAR(13) + CHAR(10)),1,2,'')
querytsql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I do not see a `Group By` in this query. Is this the whole query?
0 Likes 0 ·
This is an extreme example of creating a comma delimited list (except with CRLF delimiters). The inner queries are "grouped" by their outer query column references. More here - http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
@AbhiD - for something somewhat complex like this, it would be very helpful for you to provide create/insert scripts so others could execute the query. By looking at the query, I think you can add an additional "group" for Cusip by adding it to your final `WHERE` clause: `WHERE ttab.PershAcctNumber = atab.PershAcctNumber AND ttab.Cusip = atab.Cusip` Just remember... because you can't group by data that isn't there, this means you will also be filtering out any rows from ttab that don't have a matching Cusip column in atab.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ken..thx buddy ... it was very simple thing but i overlooked ....i guess i overcooked my head last night .... :P
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.