Better formatting for Legalities

[staghouse]

Right now the legalities table table is a bunch of UUID's with a column for one format at a time and its value, this is a bit of a mess in my opinion to work with - should we update this table to be one UUID per entry where we can have columns for each format with a value and some type of falsey value if the column is not legal. Something like this?

id	uuid	brawl	commander	duel	etc
1		"legal"	"legal"	null	etc

[drewmie]

I disagree. That's an easy PIVOT to create ourselves in SQL. To change the underlying legalities table like that means adding a column every time there's a new format. It also needlessly adds columns with a lot of null values when the format isn't applicable to a uuid.

[drewmie]

@staghouse , here are a couple of pivots which might be what you're looking for. Note that I put a "TOP 1000" limitation on them which you can comment out.

Legalities Pivot > UUID counts by Format and Status

DECLARE
     @cols AS VARCHAR(MAX)
    ,@pivot AS VARCHAR(MAX)
;

SET @cols = STUFF((SELECT distinct ','
                + QUOTENAME(c.[status]) 
            FROM legalities c
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')
;

set @pivot =
    'SELECT
    TOP 1000
    [format], ' + @cols + ' from (
        SELECT
             [format]
            ,[status]
            ,COUNT(*) AS count
        FROM legalities
        GROUP BY
             [format]
            ,[status]
        ) x
    PIVOT (
        SUM(count)
        FOR [status] IN (' + @cols + ')
        ) p '

execute(@pivot);

Legalities Pivot > Status by UUID and Format

DECLARE
     @cols AS VARCHAR(MAX)
    ,@pivot AS VARCHAR(MAX)
;

SET @cols = STUFF((SELECT distinct ','
                + QUOTENAME(c.[format]) 
            FROM legalities c
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')
;

set @pivot =
    'SELECT
    TOP 1000
    uuid, ' + @cols + ' from (
        SELECT
         uuid
        ,[format]
        ,[status]
        from legalities
        ) x
    PIVOT (
        MAX([status])
        FOR [format] IN (' + @cols + ')
        ) p '

execute(@pivot);

I'm going to work on creating a formats table so I can categorize and subcategorize the 19 formats. Then I can specify the pivot column order accordingly. For example, I'd like to see the Constructed formats next to each other and the same for Limited formats.

[ZeldaZach]

This has been addressed!