34,594
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(max)
select *,px = row_number() over (partition by [字段1] order by getdate())
into #tb
from tb
--剩下的对#tb行转列,看下精华帖,很多例子!
DECLARE @TB TABLE([F1] VARCHAR(1), [F2] FLOAT)
INSERT @TB
SELECT 'A', 0.1 UNION ALL
SELECT 'B', 0.2 UNION ALL
SELECT 'C', 0.3 UNION ALL
SELECT 'A', 0.4 UNION ALL
SELECT 'B', 0.5 UNION ALL
SELECT 'C', 0.6
;WITH C AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY GETDATE()) RN
FROM @TB
)
SELECT MAX(CASE WHEN (RN-1)%3 =0 THEN F2 END) AS A,
MAX(CASE WHEN (RN-1)%3 =1 THEN F2 END) AS B,
MAX(CASE WHEN (RN-1)%3 =2 THEN F2 END) AS C
FROM C
GROUP BY (RN-1)/3
/*
A B C
---------------------- ---------------------- ----------------------
0.1 0.2 0.3
0.4 0.5 0.6
*/
create table t1
(
vol1 varchar(5),
vol2 decimal(18,1)
)
insert into t1
select 'A', 0.1 union all
select 'B', 0.2 union all
select 'C', 0.3 union all
select 'A', 0.4 union all
select 'B', 0.5 union all
select 'C', 0.6
select
max(case vol1 when 'A' then vol2 else null end) A,
max(case vol1 when 'B' then vol2 else null end) B,
max(case vol1 when 'C' then vol2 else null end) C
from
(
select t.* , px = (select count(1) from t1 where vol1 = t.vol1 and vol2 < t.vol2) + 1 from t1 t
) m
group by px
drop table t1
/*
A B C
-------------------- -------------------- --------------------
.1 .2 .3
.4 .5 .6
(所影响的行数为 2 行)
*/
create table t1
(
vol1 varchar(5),
vol2 float
)
insert into t1
select 'A', 0.1 union all
select 'B', 0.2 union all
select 'C', 0.3 union all
select 'A', 0.4 union all
select 'B', 0.5 union all
select 'C', 0.6
;with abc as
(select row_number() over(partition by vol1 order by vol2) as row,* from t1)
--select * from abc
select max(case when vol1='A' then vol2 else null end) as 'A',
max(case when vol1='B' then vol2 else null end) as 'B',
max(case when vol1='C' then vol2 else null end) as 'C'
from abc group by row