Htykuut

I'll cut straight to the chase. I currently have reduced a larger table to one such as the following:

 Key    Group    Data1    Data2    Data3

=====  =======  =======  =======  =======

 AAA     A         1        0        0

 ASD     A         1        1        0

 FSD     A         1        0        1

 BAS     A         1        1        1

 CDR     B         1        1        1

 KLW     B         1        0        1

Here is the code to create and populate it:

CREATE TABLE #DataGroups

(

    [Key]   VARCHAR(25),

    [Group] VARCHAR(25),

    [Data1] BIT,

    [Data2] BIT,

    [Data3] BIT

)



INSERT INTO #DataGroups VALUES

('AAA', 'A', 1, 0, 0),

('ASD', 'A', 1, 1, 0),

('FSD', 'A', 1, 0, 1),

('BAS', 'A', 1, 1, 1),

('CDR', 'B', 1, 1, 1),

('KLW', 'B', 1, 0, 1)

Constraints are as follows:

• 'Key' is unique


• ('Group', 'Data1', 'Data2', 'Data3') is unique


• 'Data1' will always be 1 (however I don't know if this is pertinent to the solution, we can assume that it may not always be 1)

What I need to do is formulate a query that rearranges the data in the following manner:

 Group     Data1     Data12    Data13    Data123

=======  ========  =========  ========  =========

   A        AAA       ASD        FSD       BAS

   B        NULL      NULL       KLW       CDR

As per this table, I need to fetch the Key based on the Data values for each group. So for the Data 1 column, I need to fetch the Key in the Group where the Data1 = 1, Data2 = 0 and Data3 = 0. Let me know if I should clarify if this doesn't make sense.

I'm not too experienced with SQL, so I'd prefer as clean as a solution as possible. I know that since I'll be converting my row data to columns, I will most likely need to use PIVOT, but I'm not entirely sure. Even a little help with what type of functions / aggregates I'll need to use to get this figured out would be greatly appreciated.

Apologies as I've tried coming up with my own solution to try and get started using PIVOT, but I can't seem to work one out even close to how I want it.

You seem to want something like this:

select group,

       max(case when data1 = 1 and data2 = 0 and data3 = 0 then key end) as data1,

       max(case when data1 = 1 and data2 = 1 and data3 = 0 then key end) as data12,

       max(case when data1 = 1 and data2 = 0 and data3 = 1 then key end) as data13,

       max(case when data1 = 1 and data2 = 1 and data3 = 1 then key end) as data123

from #DataGroups dg

group by group

I would use Gordon's solution. But for fun, here's the maths/pivot based solution instead, based on the fact we can combine the bits to form a number:

declare @DataGroups table

(

    [Key]   VARCHAR(25),

    [Group] VARCHAR(25),

    [Data1] BIT,

    [Data2] BIT,

    [Data3] BIT

)



INSERT INTO @DataGroups VALUES

('AAA', 'A', 1, 0, 0),

('ASD', 'A', 1, 1, 0),

('FSD', 'A', 1, 0, 1),

('BAS', 'A', 1, 1, 1),

('CDR', 'B', 1, 1, 1),

('KLW', 'B', 1, 0, 1)



;With Basics as (

select

    [Group],[Key],Data1 + (2 * data2) + (4 * data3) as Total

from

    @DataGroups dg

)

select

    [Group],[1] as Data1,[3] as Data12,[5] as Data13,[7] as Data123

from

    Basics

        pivot

    (MAX([Key]) for Total in ([1],[3],[5],[7])) v

Note, we have to use a subquery/CTE to remove the Data1, etc columns from the result set before we pivot¹, because otherwise they form part of the implicit set of grouping columns for the pivot and we don't collapse any rows. Change the select in Basics to use *,Data1 + (2 * data2) + (4 * data3) as Total to see what that looks like.

Result:

Group    Data1    Data12   Data13   Data123

-------- -------- -------- -------- ---------

A        AAA      ASD      FSD      BAS

B        NULL     NULL     KLW      CDR

¹I'm always griping about this so decided it was time to add a feedback issue for it to see if SQL Server could be improved.