Creating permutations of the column based on same column value Sql-CodePudding

I am trying to create a cross join in order to get this result. The goal is to get permutations of a column based on a same value in the same table . This is the input :

Id	name	desc
1	apple	App
1	apple	Aps
2	apple	Apl
2	apple	Ale
2	apple	Apls
3	orange	orng

Now this the result I want only name is Similar then add it.

Id	name	desc
1	apple	App
1	apple	Aps
1	apple	Apl
1	apple	Ale
1	apple	Apls
2	apple	App
2	apple	Aps
2	apple	Apl
2	apple	Ale
2	apple	Apls

I need to do this in insert statement and if the id already has the name and desc I don’t need to add the just to avoid duplications.

I tired this Sql :

Select distinct a.id,a.name,a.desc
From table a cross join table b
Where a.name = b.name

I got the permutations but I want to remove the ones I don’t need. Also I want to do it in insert select, I am doing a distinct not sure how I can do that.

CodePudding user response：

This seems like what you are after from the sample data and expected results. I use a couple of derived tables to get the DISTINCT combinations of Id & Name, and Name & Desc, and JOIN on the Name:

SELECT *
INTO dbo.YourTable
FROM (VALUES(1,'apple','App'),
            (1,'apple','Aps'),
            (2,'apple','Apl'),
            (2,'apple','Ale'),
            (2,'apple','Apls'),
            (3,'orange','orng'))V(Id,Name,[desc]); --DESC is a reserved keyword, and should not be used for names

GO

SELECT NI.Id,
       ND.Name,
       ND.[desc]
FROM (SELECT DISTINCT
             Name,
             [Desc]
      FROM dbo.YourTable) ND
     JOIN (SELECT DISTINCT
                  Id,
                  Name
           FROM dbo.YourTable) NI ON ND.Name = NI.Name
WHERE NI.Name = 'apple';
GO

DROP TABLE dbo.YourTable;