34,838
社区成员




SELECT case when number=1 then AAA else null end as [AAA],case when number=1 then BBB else null end as [BBB],case when number=1 then CCC else null end as [CCC]
FROM
ABC CROSS JOIN(
SELECT number
FROM master..spt_values
WHERE number >= 1 and type='P' and number<=3
) b group by AAA,BBB,CCC,number
select aaa,bbb,ccc
from (
select aaa,'' bbb,'' ccc from abc group by aaa
union all
select aaa,'' bbb,'' ccc from abc group by aaa
union all
select * from abc)
b order by aaa,bbb desc
A01 B01 C01
A01
A01
A02 B02 C02
A02
A02
A03 B03 C03
A03
A03
--CTE是辅助表。就是个数字表复制2遍
with cte as
(select 1 as n union all
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3 ),
abc as
(select 'a01' as aaa,'b01' as bbb,'c01' as ccc union all
select 'a02' as aaa,'b02' as bbb,'c02' as ccc union all
select 'a03' as aaa,'b03' as bbb,'c03' as ccc )
select b.aaa,b.bbb,b.ccc from
(select *,ROW_NUMBER()over(partition by n order by n) as rn from cte )as a
left join (select *,ROW_NUMBER()over(order by aaa)as n1 from abc)as b
on a.rn=b.n1 and a.n=b.n1
order by a.n,b.aaa desc
select AAA,BBB,CCC from (
select AAA,BBB,CCC,AAA as col1,RIGHT(AAA,2) as col2 from ABC union all
select ' ',' ',' ',AAA,RIGHT(AAA,2)+1 from ABC union all
select ' ',' ',' ',AAA,RIGHT(AAA,2)+2 from ABC) a order by col1,col2
use tempdb
create table ABC(AAA VARCHAR(10),BBB VARCHAR(10),CCC VARCHAR(10))
INSERT INTO ABC
SELECT 'A01','B01','C01' UNION ALL
SELECT 'A02','B02','C02' UNION ALL
SELECT 'A03','B03','C03'
;with sel as(
select (row_number() over(order by getdate())-1)*3 as rn,* from abc
)
select a.* from sel a
right join (select number from master..spt_values where type='p') b
on a.rn=b.number
where b.number<=(select max(rn) from sel )
select AAA,BBB,CCC from (
select AAA,BBB,CCC,AAA as col1,RIGHT(AAA,2) as col2 from ABC union all
select ' ',' ',' ',AAA,RIGHT(AAA,2)+1 from ABC union all
select ' ',' ',' ',AAA,RIGHT(AAA,2)+2 from ABC) a order by col1,col2
declare @abc table
(
AAA varchar(10),
BBB varchar(10),
CCC varchar(10)
)
IF OBJECT_ID('TEMPDB..#CTE') > 0
DROP TABLE #CTE
INSERT INTO @abc VALUES
('A01', 'B01', 'C01'),
('A02', 'B02', 'C02'),
('A03', 'B03', 'C03');
WITH CTE AS
(
SELECT ROW_NUMBER()OVER(PARTITION BY AAA,BBB,CCC ORDER BY (SELECT 0)) ID,A.*,AAA ord,''ddd,''eee,''fff FROM @abc A
CROSS JOIN (SELECT TOP 3 NAME FROM SYSOBJECTS)B
),cte1 as
(
SELECT id,ord,AAA,BBB,ccc FROM CTE where ID=1
union all
select id,ord,ddd,eee,fff from CTE where ID>1
)
select aaa,bbb,ccc from cte1
order by ord,id
declare @abc table
(
AAA varchar(10),
BBB varchar(10),
CCC varchar(10)
)
IF OBJECT_ID('TEMPDB..#CTE') > 0
DROP TABLE #CTE
INSERT INTO @abc VALUES
('A01', 'B01', 'C01'),
('A02', 'B02', 'C02'),
('A03', 'B03', 'C03');
WITH CTE AS
(
SELECT ROW_NUMBER()OVER(PARTITION BY AAA,BBB,CCC ORDER BY (SELECT 0)) ID,A.* FROM @abc A
CROSS JOIN (SELECT TOP 3 NAME FROM SYSOBJECTS)B
)
SELECT * INTO #CTE FROM CTE
UPDATE A SET AAA='',BBB='',CCC=''
FROM #CTE A WHERE ID >1
SELECT AAA,BBB,CCC FROM #CTE