Concatenates all the results that meet the conditions into a column and separate them with commas.
Concatenates all the results that meet the conditions into a column and separate them with commas.
The Code is as follows:
Create table tb (id int, value varchar (10 ))
Insert into tb values (1, 'A ')
Insert into tb values (1, 'bb ')
Insert into tb values (2, 'aaa ')
Insert into tb values (2, 'bbb ')
Insert into tb values (2, 'ccc ')
Go
Create function [dbo]. [f_str] (@ id int) returns nvarchar (1000)
As
Begin
Declare @ str nvarchar (1000)
Set @ str =''
Select @ str = @ str + ',' + cast (value as nvarchar (900) from tb where id = @ id
Set @ str = right (@ str, len (@ str)-1)
Return @ str
End
Go
-- Call a function
Select id, value = dbo. f_str (id) from tb group by id
Running result:
All nvarchar in the above function is of the varchar type, and the red color of the above function is not specified when the cast method is called. After testing, my friend found that the results would be truncated at 30 characters. I thought it was the difference between varchar and nvarchar. I tried to change varchar to nvarchar, the result of a friend test is truncated at 54 characters. I checked the default length of varchar. For details, see the following description in SQL server online series:
Char and varchar
Data Types of fixed-length (char) or variable-length (varchar) characters.
Char [(n)]
Character data with a fixed length of n Bytes and is not Unicode. N must be a value between 1 and 8,000. The storage size is n Bytes. The synonym for char in the SQL-92 is character.
Varchar [(n)]
Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes. The length of the input data can be zero. The synonym for varchar in the SQL-92 is char varying or character varying.
Note
If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the CAST function, the default length is 30.
The default database sorting rules will be assigned to objects using char or varchar unless a specific sorting rule is assigned to the COLLATE clause. This sorting rule controls the code page used to store character data.
For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues. If char or varchar is used:
If you want the data values in the column to be close to the same size, use char.
If you want the data values in the column to be significantly different, use varchar.
If SET ANSI_PADDING is OFF when you execute create table or alter table, a char column defined as NULL will be processed as varchar.
When the collation code page uses double-byte characters, the storage size is still n Bytes. Depending on the string, the storage size of n Bytes may be less than n characters.
