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.