Creating permutations of the column based on same column value Sql

Question

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.

You reference a `table2` in your SQL, but you only include one table's definition in your question. Also why `Where table.name = table.name`? The only time that will *not* be TRUE is when `table.name` has the value `NULL` (as `NULL` doesn't equal itself). As such, why not just `table.name IS NOT NULL`? — Thom A, Nov 04 '22 at 10:48
Oh assume that table and table2 are both same tables as I mentioned earlier — RR8422, Nov 04 '22 at 10:49
Then don't give them different names in your SQL, that's confusing. Alias them instead. — Thom A, Nov 04 '22 at 10:50

Thom A · Accepted Answer · 2022-11-04T11:05:47.550

0

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;

edited Nov 04 '22 at 11:05

answered Nov 04 '22 at 10:56

Thom A

88,727
11
45
75

One thing though, it is adding the value which is already there for instance if the 1 has though 2 values it shouldn’t add it but in this case it is adding it . – RR8422 Nov 04 '22 at 13:14
I don't follow what you are stating there, @RR8422 . The results in the above and the expected results in your question are the same, with 10 rows returned. – Thom A Nov 04 '22 at 13:16

Creating permutations of the column based on same column value Sql

1 Answers1