SQL - self join 'n' times with condition

Question

I have the following table:

Id                                    | Type
--------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  |  1
C7B1752D-FD30-445A-AD6C-51D1434607D3  |  2
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  |  3
--------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912  |  1
546519ED-5E78-4DAD-ADFF-9DC0AA67B763  |  2
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4  |  3
--------------------------------------------
-- ... and so on ... --

Now, I need a specific type of SELECT (something like this):

SELECT
    [Id] AS [OneId]   -- Where [Type] = 1,
    [Id] AS [TwoId]   -- Where [Type] = 2,
    [Id] AS [ThreeId] -- Where [Type] = 3
FROM Table

This is what I've tried so far but with bad results:

SELECT
    oneI.[Id] AS [OneId]   -- Where [Type] = 1,
    twoI.[Id] AS [TwoId]   -- Where [Type] = 2,
    threeI.[Id] AS [ThreeId] -- Where [Type] = 3
FROM Table AS i
INNER JOIN Table AS oneI ON
    i.[Id] = oneI.[Id]
INNER JOIN Table AS twoI ON
    i.[Id] = twoI.[Id]
INNER JOIN Table AS threeI ON
    i.[Id] = threeI.[Id]
WHERE 
    oneI.[Type] = 1
AND twoI.[Type] = 2
AND threeI.[Type] = 3

Or even worse (which gave me more lots of combinations):

SELECT
    oneI.[Id] AS [OneId]   -- Where [Type] = 1,
    twoI.[Id] AS [TwoId]   -- Where [Type] = 2,
    threeI.[Id] AS [ThreeId] -- Where [Type] = 3
FROM Table AS i, TABLE AS oneI, Table AS twoI, Table AS threeI
WHERE 
    oneI.[Type] = 1
AND twoI.[Type] = 2
AND threeI.[Type] = 3

EDIT:

I would also have another column on which I would group those Ids. So the updated table look like:

Id                                               | GroupId | Type
------------------------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  -- OneId   |    1    |  1 
C7B1752D-FD30-445A-AD6C-51D1434607D3  -- TwoId   |    1    |  2 
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  -- ThreeId |    1    |  3 
------------------------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912 -- OneId    |    2    |  1 
546519ED-5E78-4DAD-ADFF-9DC0AA67B763 -- TwoId    |    2    |  2 
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4 -- ThreeId  |    2    |  3 
----------------------------------------------------------------

So, I would need the following result:

OneI   | TwoI  | ThreeI   | GroupId
------------------------------------------------------
OneId  | TwoId | ThreeId  |    1
OneId  | TwoId | ThreeId  |    2

EDIT:

I have one more special case - [Type] column can repeat:

Id                                                    | GroupId | Type
-----------------------------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  -- OneId        |    1    |  1 
C7B1752D-FD30-445A-AD6C-51D1434607D3  -- TwoId        |    1    |  2 
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  -- ThreeId      |    1    |  3 
FEB4A345-FEA0-4530-AE52-6CF4F07E37BA  -- OtherThreeId |    1    |  3 
-----------------------------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912 -- OneId         |    2    |  1 
546519ED-5E78-4DAD-ADFF-9DC0AA67B763 -- TwoId         |    2    |  2 
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4 -- ThreeId       |    2    |  3 
----------------------------------------------------------------

And now the result would be:

OneI   | TwoI  | ThreeI        | GroupId
------------------------------------------------------
OneId  | TwoId | ThreeId       |    1
OneId  | TwoId | OtherThreeId  |    1
OneId  | TwoId | ThreeId       |    2

sqlfiddle

If the results are like in groups of 3, they must have some kind of common discriminator from other groups. — Pinx0, Apr 23 '18 at 06:18

Yogesh Sharma · Accepted Answer · 2018-04-23T12:01:45.433

2

Well, this would required some sequential ordering columns, but you could also express this as

select max(case when [Type] = 1 then Id end) OneId,
       max(case when [Type] = 2 then Id end) TwoId,
       max(case when [Type] = 3 then Id end) ThreeId
from (select *, 
             row_number() over (order by (select 1)) Seq 
      from table
     ) t
group by (Seq - [Type]);

EDIT :- However, if you want to include group also then use them as in select statement as

select (Seq - [Type]) as GroupId,
       max(case when [Type] = 1 then 'OneId' end) OneI,
       max(case when [Type] = 2 then 'TwoId' end) TwoI,
       max(case when [Type] = 3 then 'ThreeId' end) ThreeI
from (select *, 
             row_number() over (order by (select 1)) Seq 
      from table
      ) t
group by (Seq - [Type]);

For your updated table you can directly use table with group by clause with your GroupId column as then you don't use row_number() function and subquery

select max(case when [Type] = 1 then 'OneId' end) OneI,
       max(case when [Type] = 2 then 'TwoId' end) TwoI,
       max(case when [Type] = 3 then 'ThreeId' end) ThreeI,
       GroupId
from table t
group by GroupId;

Demo

edited Apr 23 '18 at 12:01

answered Apr 23 '18 at 06:23

Yogesh Sharma

49,870
5
26
52

i need GroupId (it's value) insted of row number – Apr 23 '18 at 07:25
@jovanM... Changing the question after asking it is rude. Btw, check my edit. – Yogesh Sharma Apr 23 '18 at 07:28
still not the expected result from my last edit in the question - I'm still missing a row for the Id with the duplicated [Type] – Apr 23 '18 at 07:38
yea, sry for the edit, i'm doing multiple tasks at once and couldn't keep a track what i needed exactly... – Apr 23 '18 at 07:39
@jovanM... Whenever, you make an edit, and after people look at answer then it would attract the down-vote. So, it would be always better to ask other question with new edits. – Yogesh Sharma Apr 23 '18 at 07:42
Sharma, I just added condition to a queestion - i didn't change the question from the root. My question is still the same at the base... – Apr 23 '18 at 07:46
thnks, but still i need ids instead of nulls - as in the result table in my last edit.. – Apr 23 '18 at 08:08
hey, i still got slight problem with the select... if i add another row with same type - query again returns null for some ids. here's the [sqlfiddler](http://sqlfiddle.com/#!18/0c2a5/1) – Apr 23 '18 at 10:46
@jovanM... Oh yes that would not come in my mind corrected check [Here](http://sqlfiddle.com/#!18/0c2a5/12) – Yogesh Sharma Apr 23 '18 at 10:56

score 2 · Answer 2 · edited Apr 23 '18 at 06:32

You need an Grp attribute then specifies which rows are together in one group. Something like this

Id                                    | Type |  Grp
----------------------------------------------------
C1C1A90D-B131-4450-B1BF-5041F36F9144  |  1   |   1
C7B1752D-FD30-445A-AD6C-51D1434607D3  |  2   |   1
3AAF8BB6-A6D4-4780-BEF9-ACBBF75A85DE  |  3   |   1
---------------------------------------------------
67EF1537-A22E-4D2D-AAEA-FC0D9E2B9912  |  1   |   2
546519ED-5E78-4DAD-ADFF-9DC0AA67B763  |  2   |   2
8F66A3F9-C652-4758-8E17-B4DE0B0D85A4  |  3   |   2
--------------------------------------------------

Then you can use conditional aggregation like this

SELECT
     MAX(CASE WHEN [Type] = 1 THEN [Id] END) AS [OneId],
     MAX(CASE WHEN [Type] = 2 THEN [Id] END) AS [TwoId],
     MAX(CASE WHEN [Type] = 3 THEN [Id] END) AS [ThreeId]
FROM YourTable
GROUP BY Grp

score 0 · Answer 3 · answered Apr 23 '18 at 06:21

0

Looks like this would do the job for you

; WITH CTE
AS
(
SELECT
    Id,
    [Type]
    FROM YourTable
       WHERE [Type]
       IN
       (
          1,2,3,4,5
       )
)
SELECT
    *
    FROM CTE
    PIVOT
    (
       MAX(id)
       FOR [Type] IN
       (
          [1],[2],[3],[4],[5]
       )
    )Pvt

answered Apr 23 '18 at 06:21

Jayasurya Satheesh

7,826
3
22
39

this grouped them, but only the first group - it only returned one row - first group from the sample table in my question – Apr 23 '18 at 06:47

SQL - self join 'n' times with condition

3 Answers3