create table TEST(ID INT,NAME CHAR(1),NOA INT,NOB INT,NOC INT,NOD INT) insert into TEST select 1,'A',5,5,5,5 insert into TEST select 1,'A',4,4,4,4 insert into TEST select 2,'B',6,6,6,6 insert into TEST select 2,'B',7,7,7,7 GOCREATE FUNCTION F_VIEW() RETURNS @T TABLE(ID INT,NAME CHAR(1),NOA1 INT,NOB1 INT,NOC1 INT,NOD1 INT,NOA2 INT,NOB2 INT,NOC2 INT,NOD2 INT) AS BEGIN DECLARE @T1 TABLE(RID INT IDENTITY(1,1),ID INT,NAME CHAR(1),NOA INT,NOB INT,NOC INT,NOD INT) INSERT INTO @T1 SELECT * FROM TEST
INSERT INTO @T SELECT A.ID,A.NAME,A.NOA,A.NOB,A.NOC,A.NOD,B.NOA,B.NOB,B.NOC,B.NOD FROM (SELECT C.* FROM @T1 C WHERE NOT EXISTS(SELECT 1 FROM @T1 WHERE ID=C.ID AND RID<C.RID)) A LEFT JOIN (SELECT C.* FROM @T1 C WHERE (SELECT COUNT(1) FROM @T1 WHERE ID=C.ID AND RID<=C.RID)=2) B ON A.ID=B.ID
RETURN END GOSELECT * FROM DBO.F_VIEW()/* ID NAME NOA1 NOB1 NOC1 NOD1 NOA2 NOB2 NOC2 NOD2 ----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 A 5 5 5 5 4 4 4 4 2 B 6 6 6 6 7 7 7 7 */DROP FUNCTION F_VIEW DROP TABLE TEST
ID NAME NOA NOB NOC NOD 1 A 5 5 5 5 1 A 4 4 4 4 2 B 6 6 6 6 2 B 7 7 7 7 2 B 8 8 8 8---------------------------------------------- 是不是都最多有两条相同的?
drop table #t,#tt create table #t(ID INT,NAME CHAR(1),NOA INT,NOB INT,NOC INT,NOD INT) insert into #t select 1,'A',5,5,5,5 union all select 1,'A',4,4,4,4 union all select 2,'B',6,6,6,6 union all select 2,'B',7,7,7,7declare @i int,@s varchar(8000) select newid=identity(int,1,1),nid=0,* into #tt from #t update t set t.nid=t.newid-(select min(newid) from #tt where id=t.id)+1 from #tt tselect @s='',@i=max(nid) from #tt while @i>0 begin select @s=',[NOA'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOA end),[NOB'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOB end),[NOC'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOC end),[NOD'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOD end)'+@s,@i=@i-1 end --print @s exec('select id'+@s+ ' from #tt group by id')--结果 /* id NOA1 NOB1 NOC1 NOD1 NOA2 NOB2 NOC2 NOD2 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 5 5 5 5 4 4 4 4 2 6 6 6 6 7 7 7 7 */
如果需要将32行记录并作一行,用以下动态SQL: ------------------------------------------------------------------------------------------------------------------------------------生成测试数据 CREATE TABLE TEST(ID INT,NAME VARCHAR(10),NOA INT,NOB INT,NOC INT,NOD INT) INSERT INTO TEST SELECT 1,'A',5,5,5,5 INSERT INTO TEST SELECT 1,'A',4,4,4,4 INSERT INTO TEST SELECT 2,'B',6,6,6,6 INSERT INTO TEST SELECT 2,'B',7,7,7,7--借助临时表对记录进行编号 SELECT IDENTITY(INT,1,1) AS RID,0 AS KID,* INTO #T FROM TEST UPDATE A SET KID=(SELECT COUNT(*) FROM #T WHERE ID=A.ID AND RID<=A.RID) FROM #T A--拼装动态SQL语句 DECLARE @S VARCHAR(8000),@I INT SET @S='SELECT ID,NAME' SET @I=0 WHILE @I<32 BEGIN SET @I=@I+1 SET @S=@S+',NOA'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOA END)' +',NOB'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOB END)' +',NOC'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOC END)' +',NOD'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOD END)' END SET @S=@S+' FROM #T GROUP BY ID,NAME ORDER BY ID,NAME'--执行动态SQL,执行结果自己看 EXEC(@S)--删除测试数据 DROP TABLE TEST
insert into TEST select 1,'A',5,5,5,5
insert into TEST select 1,'A',4,4,4,4
insert into TEST select 2,'B',6,6,6,6
insert into TEST select 2,'B',7,7,7,7
GOCREATE FUNCTION F_VIEW()
RETURNS @T TABLE(ID INT,NAME CHAR(1),NOA1 INT,NOB1 INT,NOC1 INT,NOD1 INT,NOA2 INT,NOB2 INT,NOC2 INT,NOD2 INT)
AS
BEGIN
DECLARE @T1 TABLE(RID INT IDENTITY(1,1),ID INT,NAME CHAR(1),NOA INT,NOB INT,NOC INT,NOD INT)
INSERT INTO @T1 SELECT * FROM TEST
INSERT INTO @T
SELECT
A.ID,A.NAME,A.NOA,A.NOB,A.NOC,A.NOD,B.NOA,B.NOB,B.NOC,B.NOD
FROM
(SELECT C.* FROM @T1 C WHERE NOT EXISTS(SELECT 1 FROM @T1 WHERE ID=C.ID AND RID<C.RID)) A
LEFT JOIN
(SELECT C.* FROM @T1 C WHERE (SELECT COUNT(1) FROM @T1 WHERE ID=C.ID AND RID<=C.RID)=2) B
ON
A.ID=B.ID
RETURN
END
GOSELECT * FROM DBO.F_VIEW()/*
ID NAME NOA1 NOB1 NOC1 NOD1 NOA2 NOB2 NOC2 NOD2
----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 A 5 5 5 5 4 4 4 4
2 B 6 6 6 6 7 7 7 7
*/DROP FUNCTION F_VIEW
DROP TABLE TEST
1 A 5 5 5 5
1 A 4 4 4 4
2 B 6 6 6 6
2 B 7 7 7 7
2 B 8 8 8 8----------------------------------------------
是不是都最多有两条相同的?
create table #t(ID INT,NAME CHAR(1),NOA INT,NOB INT,NOC INT,NOD INT)
insert into #t
select 1,'A',5,5,5,5 union all
select 1,'A',4,4,4,4 union all
select 2,'B',6,6,6,6 union all
select 2,'B',7,7,7,7declare @i int,@s varchar(8000)
select newid=identity(int,1,1),nid=0,* into #tt from #t
update t set t.nid=t.newid-(select min(newid) from #tt where id=t.id)+1 from #tt tselect @s='',@i=max(nid) from #tt
while @i>0
begin
select @s=',[NOA'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOA end),[NOB'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOB end),[NOC'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOC end),[NOD'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then NOD end)'+@s,@i=@i-1
end
--print @s
exec('select id'+@s+ ' from #tt group by id')--结果
/*
id NOA1 NOB1 NOC1 NOD1 NOA2 NOB2 NOC2 NOD2
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 5 5 5 5 4 4 4 4
2 6 6 6 6 7 7 7 7
*/
固定32条相同,不会多也不会少.
既:ID=1的有32条记录
ID=2的有32条记录
...
------------------------------------------------------------------------------------------------------------------------------------生成测试数据
CREATE TABLE TEST(ID INT,NAME VARCHAR(10),NOA INT,NOB INT,NOC INT,NOD INT)
INSERT INTO TEST SELECT 1,'A',5,5,5,5
INSERT INTO TEST SELECT 1,'A',4,4,4,4
INSERT INTO TEST SELECT 2,'B',6,6,6,6
INSERT INTO TEST SELECT 2,'B',7,7,7,7--借助临时表对记录进行编号
SELECT IDENTITY(INT,1,1) AS RID,0 AS KID,* INTO #T FROM TEST
UPDATE A SET KID=(SELECT COUNT(*) FROM #T WHERE ID=A.ID AND RID<=A.RID) FROM #T A--拼装动态SQL语句
DECLARE @S VARCHAR(8000),@I INT
SET @S='SELECT ID,NAME'
SET @I=0
WHILE @I<32
BEGIN
SET @I=@I+1
SET @S=@S+',NOA'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOA END)'
+',NOB'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOB END)'
+',NOC'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOC END)'
+',NOD'+RTRIM(@I)+'=MAX(CASE KID WHEN '+RTRIM(@I)+' THEN NOD END)'
END
SET @S=@S+' FROM #T GROUP BY ID,NAME ORDER BY ID,NAME'--执行动态SQL,执行结果自己看
EXEC(@S)--删除测试数据
DROP TABLE TEST