Create view or select statement-CodePudding

Im new with SQL and have some issue to generate view. This is my current table structure :

Place	Person	Fruit
MarketA	PersonA	Apple
MarketB	PersonA	Orange
MarketB	PersonA	Apple
MarketC	PersonB	Orange
MarketA	PersonC	Apple
MarketB	PersonD

My goal is to create view like below :

Person	Fruit
PersonA	Apple/Orange
PersonB	Orange
PersonC	Apple
PersonD

Can help me on how can i achieve this?

CodePudding user response：

First of all, it will help if you add a CREATE TABLE to your question, but your table example also really helps. You can achieve this (especially in earlier versions of SQL Server) with this statement:

SELECT DISTINCT Person,
        STUFF(
                (
                    SELECT DISTINCT '/'   A.Fruit Fruits
                    FROM TableA  A
                    WHERE A.Person = B.Person
                    ORDER BY Fruits
                    FOR XML PATH(''), TYPE
                ).value('.', 'varchar(max)'),
                1,
                1,
                ''
            ) Fruit
FROM TableA B

I know in SQL 2017/2019 there is an easier way, but call me old fashioned...

CodePudding user response：

Using SQL SERVER 2017 or above:

CREATE VIEW my_view
AS
SELECT PERSON, STRING_AGG(Fruit,'/') AS Fruit
FROM (
   SELECT DISTINCT PERSON, Fruit
   FROM  groupedtable
)a
GROUP BY Person;

CodePudding user response：

Try this;

WITH groupedtable AS (
SELECT DISTINCT Person, Fruit
FROM yourtable
)
SELECT Person, STRING_AGG(Fruit, '/') AS Fruit
FROM groupedtable
GROUP BY Person;

See Demo