ID SN Type Test Date
1 a op 53.0 7/9
2 b op 52.0 7/9
3 c op 52.4 7/9
4 e op 52.3 7/10
5 f op 52.4 7/10
6 g op 52.5 7/10转化为
ID Type 7/9 7/10
1 op 53.0 52.3
2 op 52.0 52.4
3 op 52.4 52.5普通的列传行,结果为
ID Type 7/9 7/10
1 op 53.0 NULL
2 op 52.0 NULL
3 op 52.4 NULL
4 op NULL 52.3
5 op NULL 52.4
6 op NULL 52.5
1 a op 53.0 7/9
2 b op 52.0 7/9
3 c op 52.4 7/9
4 e op 52.3 7/10
5 f op 52.4 7/10
6 g op 52.5 7/10转化为
ID Type 7/9 7/10
1 op 53.0 52.3
2 op 52.0 52.4
3 op 52.4 52.5普通的列传行,结果为
ID Type 7/9 7/10
1 op 53.0 NULL
2 op 52.0 NULL
3 op 52.4 NULL
4 op NULL 52.3
5 op NULL 52.4
6 op NULL 52.5
MAX(CASE WHEM Date ='7/9' THEN Test END )AS '7/9',
MAX(CASE WHEM Date ='7/10' THEN Test END )AS '7/10'
FROM TB GROUP BY ID,TYPE
[7/9]=case when type=[7/10] then [date] end ,
[7/10]=case when type=[7/10] then [date] end
from t1 left join t2
on t1.id=t2.id
MAX(CASE WHEM Date ='7/9' THEN Test END )AS '7/9',
MAX(CASE WHEM Date ='7/10' THEN Test END )AS '7/10' INTO #T
FROM TB GROUP BY ID,TYPE
SELECT IDD=IDENTITY(INT,1,1),Type , [7/10] INTO #TT FROM #T WHERE [7/9] IS NULL
SELECT T1.ID,T1.TYPE,T1.[7/9],T2.[7/10] FROM
(
SELECT ID , Type , [7/9] FROM #T WHERE [7/10] IS NULL
)T ,
(
SELECT IDD,Type , [7/10] FROM #TT )T1
WHERE T.ID=T1.IDD
declare @T table(ID int, SN varchar(10), Type varchar(10), Test decimal(10,1), Date varchar(10))
Insert @T select
1, 'a' ,'op', 53.0 ,'7/9' union all select
2, 'b' ,'op', 52.0 ,'7/9' union all select
3, 'c' , 'op', 52.4 , '7/9' union all select
4 , 'e' , 'op', 52.3 , '7/10' union all select
5 , 'f' , 'op', 52.4 , '7/10' union all select
6 , 'g', 'op', 52.5, '7/10'
select a.id,a.type,a.test as [7/9],[7/10]=b.test from @t a
join @t b
on a.id+3=b.idid type 7/9 7/10
----------- ---------- --------------------------------------- ---------------------------------------
1 op 53.0 52.3
2 op 52.0 52.4
3 op 52.4 52.5(3 行受影响)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( ID int, SN varchar(10),Type varchar(10) ,Test decimal(19,1) , Date varchar(10))
go
insert tb1 SELECT
1, 'a' , 'op' , 53.0, '7/9' UNION ALL SELECT
2, 'b' , 'op' , 52.0, '7/9' UNION ALL SELECT
3, 'c' , 'op' , 52.4, '7/9' UNION ALL SELECT
4, 'e' , 'op' , 52.3, '7/10' UNION ALL SELECT
5, 'f' , 'op' , 52.4, '7/10' UNION ALL SELECT
6, 'g' , 'op' , 52.5, '7/10' IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2( ID int,Type varchar(10) ,[7/9] decimal(19,1) ,[7/10] decimal(19,1))
go
insert tb2 SELECT
1 , 'op' ,53.0 ,52.3 UNION ALL SELECT
2 , 'op' ,52.0 ,52.4 UNION ALL SELECT
3 , 'op' ,52.4 ,52.5
go
select id,TYPE,[7/9] , null as [7/10]
from tb2
union all
select id,TYPE,null ,test
from tb1
where ID not in (select ID from tb2 )
goid TYPE 7/9 7/10
----------- ---------- --------------------------------------- ---------------------------------------
1 op 53.0 NULL
2 op 52.0 NULL
3 op 52.4 NULL
4 op NULL 52.3
5 op NULL 52.4
6 op NULL 52.5
Insert @T select
1, 'a' ,'op', 53.0 ,'7/9' union all select
2, 'b' ,'op', 52.0 ,'7/9' union all select
3, 'c' , 'op', 52.4 , '7/9' union all select
4 , 'e' , 'op', 52.3 , '7/10' union all select
5 , 'f' , 'op', 52.4 , '7/10' union all select
6 , 'g', 'op', 52.5, '7/10' SELECT ID,TYPE,
MAX(CASE WHEN Date ='7/9' THEN Test END )AS '7/9',
MAX(CASE WHEN Date ='7/10' THEN Test END )AS '7/10' INTO #T
FROM @T GROUP BY ID,TYPE
SELECT IDD=IDENTITY(INT,1,1),Type , [7/10] INTO #TT FROM #T WHERE [7/9] IS NULL
SELECT T1.ID,T1.TYPE,T1.[7/9],T2.[7/10] FROM
(
SELECT ID , Type , [7/9] FROM #T WHERE [7/10] IS NULL
)T1 ,
(
SELECT IDD,Type , [7/10] FROM #TT )T2
WHERE T1.ID=T2.IDD--DROP TABLE #T,#TT(所影响的行数为 6 行)
(所影响的行数为 6 行)警告: 聚合或其它 SET 操作消除了空值。(所影响的行数为 3 行)ID TYPE 7/9 7/10
----------- ---------- ------------ ------------
1 op 53.0 52.3
2 op 52.0 52.4
3 op 52.4 52.5(所影响的行数为 3 行)