SQL concatenation field data and SQL concatenation Field

SQL concatenation field data and SQL concatenation Field

Query statement:

Select stuff (SELECT ',' + -- delimiter
KeyID -- query field FROM # tmp -- query data table WITH (NOLOCK) WHERE 1 = 1 -- Query condition for xml path (''), 1, -- 0: NULL 1: output 2 ++ as is: Add separator 1 first, -- N: intercept N-Len character ''-- add symbol at the beginning and end of the string)

Test statement:

If exists (SELECT * FROM tempdb .. sysobjects where id = object_id ('tempdb .. # tmp ') -- determine whether the temporary TABLE [# tmp] has a begin drop table # tmp -- delete the temporary table endcreate table # tmp -- create a temporary TABLE (KeyID VARCHAR (36 ), keyValue VARCHAR (max) -- INSERT test data to # tmp VALUES (newid (), 'Data 1') insert into # tmp VALUES (newid (), 'Data 2') -- query statement -- select stuff (SELECT ',' + -- delimiter KeyID -- query field FROM # tmp -- query data table WITH (NOLOCK) WHERE 1 = 1 -- Query condition for xml path (''), 1, -- 0: NULL 1: Original output 2 ++: First added separator 1, -- N: truncates the N-Len character ''-- the symbol added at the beginning and end of the string)/* -- Multi-field combination select stuff (SELECT ', KeyID:' + KeyID + '; KeyValue: '+ KeyValue -- query field FROM # tmp -- query data table WITH (NOLOCK) WHERE 1 = 1 -- Query condition for xml path (''), 1, -- 0: NULL 1: output 2 ++ as is: Add separator 1 first, -- N: intercept N-Len character ''-- add symbol at the beginning and end of the string )*/

Query results (note: the specific results are determined by the automatically generated KeyID ):

1. Single field:

1B0EC911-5C2B-4FDE-87BB-62981DE2030D, 0527DF18-9EDD-484D-B770-B24B13C68CA1

2. Multi-field combination:

KeyID: 33C28B1E-3F75-4119-A944-1DB7541088E0; KeyValue: data 1,

KeyID: 4ECF1696-7DB6-43C7-8182-22EEF676E2CF; KeyValue: Data 2

Note:

1. Replace the tables and fields in the same way as the temporary table query method. Thank you.