怎么能实现每条记录对应加两行空行

[tanshihou]

现在的表示
表名：ABC
AAA BBB CCC
--------------------------------------------
A01 B01 C01
A02 B02 C02
A03 B03 C03

想得到的结果是
AAA BBB CCC
--------------------------------------------
A01 B01 C01
空行1
空行2
A02 B02 C02
空行1
空行2
A03 B03 C03
空行1
空行2


必须得用游标循环表ABC，每循环一会出入数据后再插入两个空行吗
用一个select可以实现吗？

谢谢了

[jayzhihui]

一个SELECT 不好弄，试下这个

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

[reenjie]

select AAA,BBB,CCC from ( select AAA,BBB,CCC,AAA as col from ABC union select ' ',' ',' ',AAA from ABC union select ' ',' ',' ',AAA from ABC) a order by col

[jayzhihui]

非要一跳SQL只有用新增列排序也可以搞定

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

[reenjie]

引用 2 楼 reenjie 的回复:

select AAA,BBB,CCC from ( select AAA,BBB,CCC,AAA as col from ABC union select ' ',' ',' ',AAA from ABC union select ' ',' ',' ',AAA from ABC) a order by col

上面的sql有点问题，可以试一下这句sql

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 )
 
 

[xingyeyu1987]

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

[xiaodongni]

--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

[wtujedp]

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

[tanshihou]

谢谢了谢谢了

[霜寒月冷]

一句可以这样搞定

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