create table #Test (TestId      int      not NULL,
                   DateEntered datetime not NULL,
                   Data        char(1)  not NULL,
                   Seq int not NULL CHECK( Seq BETWEEN 1 And 10 )
);

insert into #Test  values (1, '2000-08-17', 'A' , 1);
insert into #Test  values (1, '2000-08-17', 'B' , 2);
insert into #Test  values (1, '2000-08-17', 'C' , 3);
insert into #Test  values (1, '2000-08-17', 'D' , 4);
insert into #Test  values (1, '2000-08-17', 'E' , 5);

insert into #Test  values (2, '2000-08-17', 'A' , 1);
insert into #Test  values (2, '2000-08-17', 'C' , 2);
insert into #Test  values (2, '2000-08-17', 'E' , 3);

SELECT * FROM #Test;
/*
TestId      DateEntered              Data Seq         
----------- ------------------------------------------------------ ---- ----------- 
          1 2000-08-17 00:00:00.000  A              1 
          1 2000-08-17 00:00:00.000  B              2 
          1 2000-08-17 00:00:00.000  C              3 
          1 2000-08-17 00:00:00.000  D              4 
          1 2000-08-17 00:00:00.000  E              5 
          2 2000-08-17 00:00:00.000  A              1 
          2 2000-08-17 00:00:00.000  C              2 
          2 2000-08-17 00:00:00.000  E              3 
*/
go
-- Objective:
-- To generate all possible combinations of the values in the data column for each test.
-- Consider the 3 values for Test = 2 i.e., A / C / E
-- The required combinations are:
-- A / C / E / AC / AE / CE / ACE

-- The SELECT statement below demonstrates a trick using the CUBE operator in SQL Server
-- The CUBE operator generates combinations of data in the grouped columns & the summaries.
-- We use this property to solve the problem:
SELECT DISTINCT * 
FROM (
SELECT MIN( t2.TestID ) AS TestID , MIN( t2.DateEntered ) AS DateEntered ,
       t2.D1 , t2.D2 , t2.D3 , t2.D4 , t2.D5
FROM (
SELECT t1.TestID , t1.DateEntered ,
        MIN( CASE t1.Seq WHEN 1 THEN t1.Data END ) AS d1 ,
        MIN( CASE t1.Seq WHEN 2 THEN t1.Data END ) AS d2 ,
        MIN( CASE t1.Seq WHEN 3 THEN t1.Data END ) AS d3 ,
        MIN( CASE t1.Seq WHEN 4 THEN t1.Data END ) AS d4 ,
        MIN( CASE t1.Seq WHEN 5 THEN t1.Data END ) AS d5
FROM #Test AS t1
GROUP BY t1.TestID , t1.DateEntered
) AS t2
GROUP BY t2.D1 , t2.D2 , t2.D3 , t2.D4 , t2.D5
WITH CUBE
) AS t3
WHERE COALESCE( t3.D1 , t3.D2 , t3.D3 , t3.D4 , t3.D5 ) IS NOT NULL;

-- Output of the derived table aliased as T1
-- This is a simple cross-tab of the data using the Seq column.
/*
TestID DateEntered             d1   d2   d3   d4   d5   
------ ----------------------- ---- ---- ---- ---- ---- 
     1 2000-08-17 00:00:00.000 A    B    C    D    E
     2 2000-08-17 00:00:00.000 A    C    E    NULL NULL
*/

-- Output of the next derived table using the CUBE operator &
-- grouping on d1 , d2 , d3 , d4 & d5. Shown only for 2 because of
-- of the explosion of the rows when using CUBE. See BOL for more details.
/*
        As you can see from the output , we have got the combinations
        that we wanted + some extra rows because of the super summaries
        generated by the CUBE operator. These rows has NULL for columns
        d1 , d2 , d3 , d4 & d5. Typically you would use GROUPING to determine
        which is an actual NULL data & which one is the summary row. For our
        example , the aggregate function that we used is a simple MIN & we can
        just eliminate all these NULL values. This is achieved finally by using
        the DISTINCT query with the COALESCE where clause.
*/
/*
TestID      DateEntered              D1   D2   D3   D4   D5   
----------- ------------------------ ---- ---- ---- ---- ---- 
          2 2000-08-17 00:00:00.000  NULL NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    E    NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  A    C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    C    E    NULL NULL
          2 2000-08-17 00:00:00.000  A    C    E    NULL NULL
          2 2000-08-17 00:00:00.000  A    C    E    NULL NULL
          2 2000-08-17 00:00:00.000  A    C    E    NULL NULL
*/


-- Final output ( shown for TestID = 2 only ):
/*
TestID      DateEntered              D1   D2   D3   D4   D5   
----------- ------------------------ ---- ---- ---- ---- ---- 
          2 2000-08-17 00:00:00.000  NULL NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  NULL C    E    NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    NULL E    NULL NULL
          2 2000-08-17 00:00:00.000  A    C    NULL NULL NULL
          2 2000-08-17 00:00:00.000  A    C    E    NULL NULL
*/
This page was last updated on May 01, 2006 04:28 PM.