SQL about GROUP by merges a field value for the details of each record to extract the method

After using group by to write a statistical statement, there is a need to view the details of each record,

The first thing that comes to mind is to splice the query criteria according to the group by condition.

But the conditions are a bit more, stitching up not only trouble, but also error-prone,

So the idea of having the ID of the detail record in the form of a comma-delimited string (' 1 ', ' 2 ', ' 3 ') is followed by the group by, so it's easy to get the detail record.

Or the method found in the Universal Blog Park:

Selectroute_code,domain_id,type_id,COUNT(ID0) asCNT,Stuff(     (             --The --stuff function is used to delete characters of a specified length and to insert another set of characters at the specified starting point         Select ','+cast(ID0 as varchar)---Field Stitching          fromPms_t_d_assetinfo TwhereT.route_code=Pms_t_d_assetinfo.route_code andt.domain_id=pms_t_d_assetinfo.domain_id andt.type_id=pms_t_d_assetinfo.type_id forXML Path ("')     )     , 1,1,"') asIdstr--stuff Replace the empty string of parameter 4 with the 1th (parameter 2) character position of parameter 1 string 1 (parameter 3) length fromPms_t_d_assetinfoGroup  byroute_code,domain_id,type_id

The results are as follows:

The effect looks very good,

But

Statistical data after the big, this splicing field will be very long, finally think about it or not, as I directly splicing query conditions it,

However, this method is still good, in the stitching field is clearly limited in the case can be used!

At last

Attach a new understanding of the stuff function introduction:

First, the role

Deletes a character of the specified length and inserts another set of characters at the specified starting point.

Second, the grammar

STUFF (character_expression, start, length, character_expression)

Parameters

character_expression
A character data expression. A character_expression can be a constant, a variable, a character column, or a binary data row.

Start
An integer value that specifies the start position of the delete and insert. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Start can be a bigint type.

Length
An integer that specifies the number of characters to delete. If length is longer than the first character_expression, it is removed up to the last character in the last character_expression. Length can be a bigint type.

return type
If Character_expression is a supported character data type, character data is returned. If character_expression is a supported binary data type, binary data is returned.

Example:

Select STUFF('ABCDEFG',1,0,'1234')--The result is ' 1234ABCDEFG 'Select STUFF('ABCDEFG',1,1,'1234')--The result is ' 1234BCDEFG 'Select STUFF('ABCDEFG',2,1,'1234')--The result is ' A1234CDEFG 'Select STUFF('ABCDEFG',2,2,'1234')--The result is ' A1234DEFG '

SQL about GROUP by merges a field value for the details of each record to extract the method