If the requirement is mandatory, then I need a * in front, and if it isn't mandatory, then no * in front. I added the following into the unions of the view, and it pulls the data, but if the movement has more than one stop with the same requirement(s) it list them all, and I only need single values. This is the script that I inserted

Select Top 1 STRING_AGG (iif( lrq_manditory = 'Y' ,CONCAT ('*',lrq_equip_type,' ',lrq_type),CONCAT(lrq_equip_type,' ',lrq_type)),', ')
WITHIN GROUP (Order by lrq_equip_type,lrq_type)
from loadrequirement Where mov_number = oh.mov_number

and this is the results

DRV FST, *DRV UVAX, DRV FST, *DRV UVAX, DRV FST, *DRV UVAX, DRV FST, *DRV UVAX

Where you have 4 stops that each require a UVAX and FST, but if there are duplicate requirements, I only need to see one.

CodePudding user response：

Here's a simple example:

DECLARE @data table ( lrq_manditory varchar(1), lrq_equip_type varchar(50), lrq_type varchar(50) );
INSERT INTO @data ( lrq_manditory, lrq_equip_type, lrq_type ) VALUES 
    ( 'N', 'DRV', 'FST' ), ( 'Y', 'DRV', 'UVAX' ), ( 'N', 'DRV', 'FST' ), ( 'Y', 'DRV', 'UVAX' ), ( 'N', 'DRV', 'FST' ), ( 'Y', 'DRV', 'UVAX' ), ( 'N', 'DRV', 'FST' ), ( 'Y', 'DRV', 'UVAX' );

SELECT STRING_AGG ( list, ',' ) AS list_types FROM (
    SELECT DISTINCT
            CONCAT ( CASE lrq_manditory WHEN 'Y' THEN '*' ELSE '' END, lrq_equip_type, SPACE(1), lrq_type ) AS list
    FROM @data
) AS x;

RETURNS

 ------------------- 
|    list_types     |
 ------------------- 
| *DRV UVAX,DRV FST |
 ------------------- 

So perhaps something like:

SELECT STRING_AGG ( list, ',' ) AS list_types FROM (

    SELECT DISTINCT
        CONCAT ( CASE lrq_manditory WHEN 'Y' THEN '*' ELSE '' END, lrq_equip_type, SPACE(1), lrq_type ) AS list
    FROM loadrequirement WHERE mov_number = oh.mov_number

) AS x;

This is an example as I do not know your schema.

CodePudding user response：

Here is a conceptual example for you.

It follows the same minimal reproducible example paradigm for answers too. You copy it as-is to SSMS, execute it, and it is working.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, state CHAR(2), city VARCHAR(20));
INSERT @tbl (state, city) VALUES
('FL', 'Miami'),
('FL', 'Orlando'),
('FL', 'Miami'),
('TX', 'Dallas'),
('TX', 'Austin');
-- DDL and sample data population, end

-- duplicate cities
SELECT state 
    , STRING_AGG(city, ', ') AS cityList
FROM @tbl AS t
GROUP BY t.state;

-- first, filter out duplicate cities 
;WITH rs AS
(
    SELECT state, city 
    FROM @tbl
    GROUP BY state, city
)
SELECT state 
    , STRING_AGG(city, ', ') AS cityList
FROM rs AS t
GROUP BY t.state;;

Output