SQL Rows to Columns, PIVOT probably would work but not sure how with multiple columns_问答_开发者

I've got a poorly designed table, that has a mixture of rows and columns, where it probably should have had 1 row with many many columns or some other design entirely. But that mistake was 20 years ago, on someone else's watch.

Right now I'm accomplishing the view I want with a myriad of self-joins. This is painfully slow.

The following will set up how I'm doing at the moment:

declare @client table
(
    clientNumber int,
    name    varchar(10)
)

insert into @client values (1, 'Bob');
insert into @client values (2, 'Alice');

declare @options table
(
    clientNumber int,
    optionKey varchar(4),
    optionValue1 int,
    optionValue2 int,
    optionValue3 int
)
insert into @options values (1, 'optA', 1, 1, 0);
insert into @options values (1, 'optB', 0, 1, 0);
insert into @options values (2, 'optA', 1, 1, 1);
insert into @options values (2, 'optC', 0, 0, 1);

select c.clientNumber, c.name, 
        oA.optionValue1 as [Graduated], 
        oA.optionValue2 as [Employed], 
        oA.optionValue3 as [Married], 
        oB.optionValue1 as [HasPets], 
      开发者_StackOverflow中文版  oB.optionValue2 as [LikesThai], 
        oB.optionValue3 as [MathWiz], 
        oC.optionValue1 as [DrvLicense], 
        oC.optionValue2 as [Registered], 
        oC.optionValue3 as [Outdoorsy] 
    from @client c
    left outer join @options oA 
        on oA.clientNumber = c.clientNumber and oA.optionKey = 'optA'
    left outer join @options oB 
        on oB.clientNumber = c.clientNumber and oB.optionKey = 'optB'
    left outer join @options oC 
        on oC.clientNumber = c.clientNumber and oC.optionKey = 'optC'

For these results:

SQL Rows to Columns, PIVOT probably would work but not sure how with multiple columns

The result set is exactly what I want. Not every client has an A, B, or C record so the null in the result set is fine. I can't find an example like this after a while of searching so I'm not sure PIVOT is really what I'm looking for. Suggestions?

Update: this seems to produce the same results. I'm going to test it on the much larger case to see if it's faster than all of the self-joins. (I suspect it is). I'd still like to know if I'm barking up the wrong tree with pivots.

select clientNumber,
    Min(Case o.optionKey when 'optA' then o.optionValue1 end) [Graduated],
    Min(Case o.optionKey when 'optA' then o.optionValue2 end) [Employed],
    Min(Case o.optionKey when 'optA' then o.optionValue3 end) [Married],
    Min(Case o.optionKey when 'optB' then o.optionValue1 end) [HasPets],
    Min(Case o.optionKey when 'optB' then o.optionValue2 end) [LikesThai],
    Min(Case o.optionKey when 'optB' then o.optionValue3 end) [MathWix],
    Min(Case o.optionKey when 'optC' then o.optionValue1 end) [DrvLicense],
    Min(Case o.optionKey when 'optC' then o.optionValue2 end) [Registered],
    Min(Case o.optionKey when 'optC' then o.optionValue3 end) [Outdoorsy]
    from @options o
    group by clientnumber

Here are a couple of options. Both solutions use a technique that pivots the data. The first solution will change your NULLs to 0.

SELECT c.clientNumber, c.name, 
    MAX(CASE WHEN o.optionKey = 'optA' THEN o.optionValue1 ELSE 0 END) AS [Graduated], 
    MAX(CASE WHEN o.optionKey = 'optA' THEN o.optionValue2 ELSE 0 END) AS [Employed], 
    MAX(CASE WHEN o.optionKey = 'optA' THEN o.optionValue3 ELSE 0 END) AS [Married], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN o.optionValue1 ELSE 0 END) AS [HasPets], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN o.optionValue2 ELSE 0 END) AS [LikesThai], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN o.optionValue3 ELSE 0 END) AS [MathWiz], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN o.optionValue1 ELSE 0 END) AS [DrvLicense], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN o.optionValue2 ELSE 0 END) AS [Registered], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN o.optionValue3 ELSE 0 END) AS [Outdoorsy] 
FROM @client c
LEFT OUTER JOIN @options o 
    ON o.clientNumber = c.clientNumber
GROUP BY c.clientNumber, c.name
ORDER BY c.clientNumber, c.name

The second solution preserves the NULL values. However, it requires an explicit type conversion from BIT to TINYINT, because the MAX function fails on BIT data types.

SELECT c.clientNumber, c.name, 
    MAX(CASE WHEN o.optionKey = 'optA' THEN CAST (o.optionValue1 AS TINYINT) END) AS [Graduated], 
    MAX(CASE WHEN o.optionKey = 'optA' THEN CAST (o.optionValue2 AS TINYINT) END) AS [Employed], 
    MAX(CASE WHEN o.optionKey = 'optA' THEN CAST (o.optionValue3 AS TINYINT) END) AS [Married], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN CAST (o.optionValue1 AS TINYINT) END) AS [HasPets], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN CAST (o.optionValue2 AS TINYINT) END) AS [LikesThai], 
    MAX(CASE WHEN o.optionKey = 'optB' THEN CAST (o.optionValue3 AS TINYINT) END) AS [MathWiz], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN CAST (o.optionValue1 AS TINYINT) END) AS [DrvLicense], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN CAST (o.optionValue2 AS TINYINT) END) AS [Registered], 
    MAX(CASE WHEN o.optionKey = 'optC' THEN CAST (o.optionValue3 AS TINYINT) END) AS [Outdoorsy] 
FROM @client c
LEFT OUTER JOIN @options o 
    ON o.clientNumber = c.clientNumber
GROUP BY c.clientNumber, c.name
ORDER BY c.clientNumber, c.name

I am not sure about its performance, but I would do it this way:

select c.clientNumber, c.name, 
    oA.optionValue1 as [Graduated], 
    oA.optionValue2 as [Employed], 
    oA.optionValue3 as [Married], 
    oB.optionValue1 as [HasPets], 
    oB.optionValue2 as [LikesThai], 
    oB.optionValue3 as [MathWiz], 
    oC.optionValue1 as [DrvLicense], 
    oC.optionValue2 as [Registered], 
    oC.optionValue3 as [Outdoorsy] 
from @client c 
     OUTER APPLY (SELECT * FROM @options WHERE optionkey = 'optA' And clientnumber = c.clientnumber) oA
     OUTER APPLY (SELECT * FROM @options WHERE optionkey = 'optB' And clientnumber = c.clientnumber) oB
     OUTER APPLY (SELECT * FROM @options WHERE optionkey = 'optC' And clientnumber = c.clientnumber) oC

or CTE might be useful

with t as
(select oA.ClientNumber, 
    oA.optionValue1 as [Graduated], 
    oA.optionValue2 as [Employed], 
    oA.optionValue3 as [Married], 
    oB.optionValue1 as [HasPets], 
    oB.optionValue2 as [LikesThai], 
    oB.optionValue3 as [MathWiz], 
    oC.optionValue1 as [DrvLicense], 
    oC.optionValue2 as [Registered], 
    oC.optionValue3 as [Outdoorsy] 
from 
     (SELECT * FROM @options WHERE optionkey = 'optA') oA
     OUTER APPLY (SELECT * FROM @options WHERE optionkey = 'optB' And clientnumber = oA.clientnumber) oB
     OUTER APPLY (SELECT * FROM @options WHERE optionkey = 'optC' And clientnumber = oA.clientnumber) oC )

 select c.clientNumber, c.name,
    t.[Graduated], 
    t.[Employed], 
    t.[Married], 
    t.[HasPets], 
    t.[LikesThai], 
    t.[MathWiz], 
    t.[DrvLicense], 
    t.[Registered], 
    t.[Outdoorsy] 
from @client c join t on c.clientnumber = t.clientnumber