Scalable alternative to dozens of joins to the same table?

Question

I have a base table with primary key BaseTableID and an events table that represents multiple events for a single record in the base table, i.e.

BaseTableID | EventCode | EventSequenceNumber
------------|-----------|--------------------
1           | A123      | 1
1           | A557      | 2
1           | 45AB      | 3
1           | 0987      | 4
...
1           | SD12      | 70
2           | Z902      | 1
2           | D92C      | 2
... etc ...

I need to denormalize this in order to provide a flat file in the format:

BaseTableID | Event01 | Event02 | Event03 | ... | Event70
------------|---------|---------|---------|-----|---------
1           | A123    | A557    | 45AB    | ... | SD12
2           | Z902    | D92C    |
... etc ...

I can currently achieve this with the query

select BaseTable.BaseTableID,
       Event01 = Event01.EventCode,
       Event02 = Event02.EventCode,
       Event03 = Event03.EventCode,
       Event04 = Event04.EventCode,
       ...
from BaseTable

left join Events Event01
on BaseTable.BaseTableID = Event01.BaseTableID
and Event01.EventSequenceNumber = 1

left join Events Event02
on BaseTable.BaseTableID = Event02.BaseTableID
and Event02.EventSequenceNumber = 2

left join Events Event03
on BaseTable.BaseTableID = Event03.BaseTableID
and Event03.EventSequenceNumber = 3

left join Events Event04
on BaseTable.BaseTableID = Event04.BaseTableID
and Event04.EventSequenceNumber = 4

... etc...

Which works, but scales horribly and the number of joins / columns is entirely dependent on select max(EventSequenceNumber) from Events, which could (and is likely to) change over time. Imagine the nightmare situation when one base table record ends up with hundreds of events. I don't want to copy-paste hundreds of joins!

(Worth noting - the format of required output is well outside my control. I can't change that to something more sensible)

There must be a better way. Is there?

Have a look here: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query to get a direction. — StephaneM, Jun 02 '15 at 09:32

score 0 · Answer 1 · answered Jun 02 '15 at 10:12

This will works..

CREATE  TABLE temp(BaseTableID INT, EventCode VARCHAR(10), EventSequenceNumber INT)
DECLARE @EventSequenceNumber VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)

INSERT INTO temp(BaseTableID,EventCode,EventSequenceNumber)
SELECT 1  ,' A123', 1 UNION
SELECT 1  ,' A557 ', 2 UNION
SELECT 1 ,'  45AB  ', 3 UNION
SELECT 2  ,' 0987', 4

SELECT @EventSequenceNumber= ISNULL(@EventSequenceNumber+',','')+'Event'+
CAST(EventSequenceNumber AS VARCHAR) FROM temp ORDER BY EventSequenceNumber


SET @Query='SELECT BaseTableID,'+@EventSequenceNumber+' FROM (SELECT BaseTableID,EventCode,
''Event''+CAST(EventSequenceNumber AS VARCHAR)AS EventSequenceNumber
FROM temp)pvt PIVOT 
(MAX(EventCode) FOR EventSequenceNumber IN ('+@EventSequenceNumber+
'))dst'

execute(@Query)
DROP TABLE temp

score 0 · Answer 2 · edited May 23 '17 at 12:14

I ended up pivoting as follows. It's not 100% scalable (will need manually adjusting if events go over 100), but for the required purposes, strikes a usable balance without needing to use dynamic SQL and strings.

with EventsPivot as (
    select
        BaseTableID,
        Event01 = [01], Event02 = [02], Event03 = [03], Event04 = [04], Event05 = [05], 
        Event06 = [06], Event07 = [07], Event08 = [08], Event09 = [09], Event10 = [10], 
        Event11 = [11], Event12 = [12], Event13 = [13], Event14 = [14], Event15 = [15], 
        Event16 = [16], Event17 = [17], Event18 = [18], Event19 = [19], Event20 = [20]
    from (
        select BaseTableID, EventSequenceNumber, EventCode
        from Events
    ) a
    pivot (
        max(EventCode) for EventSequenceNumber in (
            [01], [02], [03], [04], [05], [06], [07], [08], [09], [10],
            [11], [12], [13], [14], [15], [16], [17], [18], [19], [20]
        )
    ) p
)
select
    BaseTableID,
    Event01 = EventsPivot.Event01,
    Event02 = EventsPivot.Event01,
    Event03 = EventsPivot.Event01,
    ...
from BaseTable

left join EventsPivot
on BaseTable.BaseTableID = EventsPivot.BaseTableID

You can achieve a truly scalable version with dynamic SQL, at a readability/maintainability cost (adapted from https://stackoverflow.com/a/10404455/2061621, go upvote that if it's useful):

-- creates '[01],[02],[03]' etc, though order not guaranteed
declare @shortCols as nvarchar(max) =
    stuff((
        select distinct Value = ',' + quotename(right('0' + cast(EventSequenceNumber as varchar), 2))
        from Events
        order by Value
        for xml path(''), 
        type).value('.', 'nvarchar(max)'), 1, 1, '')

-- creates 'Event01 = EventsPivot.[01],Event02 = EventsPivot.[02],Event03 = EventsPivot.[03]' etc, though order not guaranteed
declare @longCols as nvarchar(max) =
    stuff((
        select distinct Value = ',' + quotename('Event' + right('0' + cast(EventSequenceNumber as varchar), 2)) + ' = ' + quotename(right('0' + cast(EventSequenceNumber as varchar), 2))
        from Events
        order by Value
        for xml path(''), 
        type).value('.', 'nvarchar(max)'), 1, 1, '')

exec('
    with EventsPivot as (
        select
            BaseTableID,
            ' + @shortCols + '
        from (
            select BaseTableID, EventSequenceNumber, EventCode
            from Events
        ) a
        pivot (
            max(EventCode) for EventSequenceNumber in (
                ' + @shortCols + '
            )
        ) p
    )
    select
        BaseTable.BaseTableID,
        ' + @longCols + '
    from BaseTable
    left join EventsPivot
    on BaseTable.BaseTableID = EventsPivot.BaseTableID
')

Scalable alternative to dozens of joins to the same table?

2 Answers2