IF EXISTS(SELECT name |
FROM sysobjects |
WHERE name = N'T' |
AND type = 'U') |
DROP TABLE T |
GO |
CREATE TABLE T ( |
C1 INT, |
C2 CHAR(8), |
C3 CHAR(8) |
) |
GO |
INSERT INTO T |
SELECT 1,'AA','K' UNION ALL |
SELECT 2,'AA','L' UNION ALL |
SELECT 3,'BB','K' UNION ALL |
SELECT 4,'BB','L' UNION ALL |
SELECT 5,'CC','K' UNION ALL |
SELECT 6,'CC','L' UNION ALL |
SELECT 7,'DD','K' UNION ALL |
SELECT 8,'DD','L' UNION ALL |
SELECT 9,'EE','K' UNION ALL |
SELECT 10,'FF','L' |
go |
select * from t |
go |
select C1, |
CD=(select Case C2 when 'AA' then 10 |
when 'BB' then 20 |
when 'CC' then 30 |
when 'DD' then 40 |
when 'EE' then 50 |
when 'FF' then 60 end from t t1 where t1.c1=t3.c1 group by C2)*10 |
+(select Case C3 when 'K' then 1 |
when 'L' then 2 end from t t2 where t2.c1=t3.c1 group by C3),C2,C3 from t t3 |
select top 1 newid(), * from t order by newid()