-1

I am using SQL SERVER 2016.

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'

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

Kindly share suggestion

Mitali Nath
  • 11
  • 4
  • 2
    If you're having problems with a specific query feel free to edit your question to include that to show what you've tried, but StackOverflow isn't a free code writing service. – AlwaysLearning Dec 02 '20 at 12:20

1 Answers1

0

You are looking for string aggregation:

select VenName, Ctypes, country,
       string_agg(purpose, '; ') within group (order by purpose) as purposes
from @testtable tt
group by VenName, Ctypes, country,
         (case when VenName <> 'Organisation' then purpose end);

Note the additional GROUP BY expression to keep the non-organizations split into separate rows.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786