CODEWITHSUNDEEP
sqlsql-server
AliceSmith
AliceSmith

Reputation: 371

SQL Query for table with multiple rows per ID outputting single row per ID

This is a bit of a head scratcher for me. I've simplified the tables and added an example scenario to assist with the context. I need to write a query in SQL Server that will output the results in the third table using the data in the first table by way of the reference table in the center. I'm not super clever with writing SQL queries (but certainly getting better), so any assistance you can provide me with would be great! The tables are as follows:

Below is the data table that may contain between one and three entries for a single Identity.

┌────────┬──────────────────┐
│Identity│Partial_Identifier│
├────────┼──────────────────┤
│100     │a                 │
├────────┼──────────────────┤
│100     │b                 │
├────────┼──────────────────┤
│100     │c                 │
├────────┼──────────────────┤
│101     │b                 │
├────────┼──────────────────┤
│102     │b                 │
├────────┼──────────────────┤
│102     │c                 │
└────────┴──────────────────┘

Below is a reference table, that matches partial identifier combinations to a single (unique) IDCode that I'll need for display purposes. The design is not something I would consider to be ideal, but that's pre-existing, so I have to make-do with it.

┌──────┬────────────────────┬────────────────────┬────────────────────┐
│IDCode│Partial_Identifier_1│Partial_Identifier_2│Partial_Identifier_3│
├──────┼────────────────────┼────────────────────┼────────────────────┤
│1     │a                   │                    │                    │
├──────┼────────────────────┼────────────────────┼────────────────────┤
│2     │a                   │b                   │                    │
├──────┼────────────────────┼────────────────────┼────────────────────┤
│3     │a                   │b                   │c                   │
├──────┼────────────────────┼────────────────────┼────────────────────┤
│4     │b                   │                    │                    │
├──────┼────────────────────┼────────────────────┼────────────────────┤
│5     │b                   │c                   │                    │
├──────┼────────────────────┼────────────────────┼────────────────────┤
│6     │b                   │c                   │d                   │
└──────┴────────────────────┴────────────────────┴────────────────────┘

For the data in the first table, I would want the following result:

┌────────┬──────┐
│Identity│IDCode│
├────────┼──────┤
│100     │3     │
├────────┼──────┤
│101     │4     │
├────────┼──────┤
│102     │5     │
└────────┴──────┘

Any assistance you might be able to provide regarding how to approach this bit of funkiness would be much appreciated.

Upvotes: 1

Views: 247

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24410

Probably not the most efficient way, but this will work:

declare @a table (id int, p_id nchar(1))
insert @a
select 100,'a'
union select 100,'b'
union select 100,'c'
union select 101,'b'
union select 102,'b'
union select 102,'c'

declare @b table (idcode int, p_id1 nchar(1), p_id2 nchar(1), p_id3 nchar(1))
insert @b
select 1, 'a', null, null
union select 2, 'a', 'b', null
union select 3, 'a', 'b', 'c'
union select 4, 'b', null, null
union select 5, 'b', 'c', null
union select 6, 'b', 'c', 'd'

select id, idcode
from 
(
    select id
    , max(case when r=1 then p_id end) a
    , max(case when r=2 then p_id end) b
    , max(case when r=3 then p_id end) c
    from (
        select id, p_id, row_number() over (partition by id order by p_id) r
        from @a
    ) x
    group by id
) y
inner join @b b
on coalesce(b.p_id1,'') = coalesce(y.a,'')
and coalesce(b.p_id2,'') = coalesce(y.b,'')
and coalesce(b.p_id3,'') = coalesce(y.c,'')
order by id

Upvotes: 1

Related Questions