I recently helped someone with performing some simple cross-tabs in SQL ... read more...
I thought I'd post a blog showing a technique I haven't seen elsewhere. This is from a recent post of mine at the SqlTeam forums.
The problem: you have two tables, each containing mutiple rows of data per “SetID”. You need to compare the two tables to see which sets have complete matches.
First, the DDL and the data:
create table #t1
(SetID int not null,
Attribute varchar(10) not null,
Value int not null,
constraint t1_pk primary key (SetID, Attribute))
create table #t2
(SetID int not null,
Attribute varchar(10) not null,
Value int not null,
constraint t2_pk primary key (SetID, Attribute))
GO
insert into #t1
select 1,'a',1 union
select 1,'b',2 union
select 1,'c',3 union
select 2,'a',4 union
select 2,'c',5...