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;
go
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;
|