Concatenate values of a specific column seperated by semi colon in sql server 2016

Question

Hello,

How can we concatenate values of a specific column seperated by semi colon in sql server 2016

DDL:

DECLARE @TESTTABLE TABLE
        (
        VenNumber INT,
        VenName VARCHAR(60),
        Ctypes  varchar(50),
        Purpose varchar(500),
        Country varchar(20)
        )



Insert @TESTTABLE

SELECT 23248,'Automoci','Organisation','Business','USA' UNION ALL
SELECT 23248,'Automoci','Organisation','Purchase order confirmation','USA' UNION ALL
SELECT 23248,'Automoci','Organisation','Supply Chain Contact','USA' UNION ALL
SELECT 23642,'Palau','Person','Business','ES' UNION ALL
SELECT 23642,'Palau','Person','Supply Chain Contact','ES' UNION ALL
SELECT 23642,'Palau','Person','Invoice','ES'

Only when values of VenNumber is same and ctypes = 'Organisation' then concatenate purpose value seperated by semicolon (;)

Output:

 23248  Automoci    Organisation    Business;Purchase order confirmation;Supply Chain Contact   USA
 23642  Palau   Person  Business    ES
 23642  Palau   Person  Supply Chain Contact    ES
 23642  Palau   Person  Invoice ES

Answer 1

Please try the following solution. It will work starting from SQL Server 2008 onwards.

SQL

--DDL and sample data population, start
DECLARE @tbl TABLE
(
 VenNumber INT,
 VenName VARCHAR(60),
 Ctypes  varchar(50),
 Purpose varchar(500),
 Country varchar(20)
);
INSERT INTO @tbl VALUES
(23248,'Automoci','Organisation','Business','USA'),
(23248,'Automoci','Organisation','Purchase order confirmation','USA'),
(23248,'Automoci','Organisation','Supply Chain Contact','USA'),
(23642,'Palau','Person','Business','ES'),
(23642,'Palau','Person','Supply Chain Contact','ES'),
(23642,'Palau','Person','Invoice','ES');
--DDL and sample data population, end

DECLARE @separator CHAR(1) = ';';

SELECT VenNumber,VenName,Ctypes 
  , STUFF((SELECT @separator + CAST(Purpose AS VARCHAR(500)) AS [text()]
  FROM @tbl AS O
  WHERE O.VenNumber = C.VenNumber 
  FOR XML PATH('')), 1, 1, NULL) AS PurposeList
 , Country
FROM @tbl AS c
WHERE Ctypes = 'Organisation'
GROUP BY VenNumber,VenName,Ctypes,Country
UNION ALL
SELECT *
FROM @tbl
WHERE Ctypes <> 'Organisation';

Answer 2

Try this:

 SELECT VenNumber,VenName,Ctypes, STRING_AGG(Purpose,';') AS Purpose, Country
 FROM @TESTTABLE
 WHERE Ctypes = 'Organisation'
 GROUP BY VenNumber,VenName,Ctypes,Country
 UNION ALL
 SELECT *
 FROM @TESTTABLE
 WHERE Ctypes <> 'Organisation'

Answer 3

Before you post again, would you please read any book on RDBMS and SQL? By definition, a table must have a key. By definition, a column has to be a scalar value, not a concatenated list! Putting the word "table" or using the affix "tbl" is a violation of ISO 11179 naming rules. It's so bad it has a name. It's called a "tibble" and we laugh at it. Since a column is a scalar value, by definition, its name cannot be a plural like you did. When you use oversized columns, you invite bad data as well as making a mess for display purposes. Did you know the ISO country code has a three letter version? I also find it interesting that you seem to to not use the DUNS as the identifier for the vendors; surely you are not using an integer to identify them! Again, this is a basic principle of modeling

CREATE TABLE Foobar_Tests
(vendor_nbr INTEGER NOT NULL,
vendor_name VARCHAR(60) NOT NULL,
vendor_type VARCHAR(20) NOT NULL
CHECK (vendor_type IN ('organization', 'person'),
test_purpose VARCHAR(50) NOT NULL,
country_code CHAR(3) NOT NULL,
PRIMARY KEY (vendor_nbr, test_purpose) --- my best guess
);

why are you using the old Sybase syntax for inserting rows in the table? Microsoft has had the ANSI/ISO standard syntax for many years.

INSERT INTO Foobar_Tests
VALUES
( 23248, 'Automoci', 'Organisation', 'Business', 'USA'),
( 23248, 'Automoci', 'Organisation', 'Purchase order confirmation', 'USA'),
( 23248, 'Automoci', 'Organisation', 'Supply Chain Contact', 'USA'),
( 23642, 'Palau', 'Person', 'Business', 'ESP'),
( 23642, 'Palau', 'Person', 'Supply Chain Contact', 'ESP'),
( 23642, 'Palau', 'Person', 'Invoice', 'ESP');

> Only when values of vendor_nbr are same and vendor_type = 'Organisation' then concatenate test_purpose value separated by semicolon (;) <<

No. If you want to convince me that this is a good way to do it, just disprove the last 40+ years of RDBMS and all the time I spent writing the standards for SQL.