现在检索结果如下 cell cellr bcchno
t1 t2 78
t1 t3 78
t5 t6 72转换成
cell bcchno
t1 78
t2 78
t1 78
t3 78
t5 72
t6 72sql语句怎么写高手指教
t1 t2 78
t1 t3 78
t5 t6 72转换成
cell bcchno
t1 78
t2 78
t1 78
t3 78
t5 72
t6 72sql语句怎么写高手指教
t1 t2 78
t1 t3 78
t5 t6 72 --select cell ,bcchno
from ta
union all
select cellr ,bcchno
from ta
(
cell nvarchar(20),
cellr nvarchar(20),
bcchno nvarchar(20)
)
insert into #AB select 't1','t2','78'
union all select 't1','t3','78'
union all select 't5','t6','72'select cell,bcchno from #AA
union all
select cellr,bcchno from #AA
union all
select cellr,bcchno from #AB
t1
t1
t5
t2
t3
t6我要的是
t1
t2
t1
t3
t5
t6
INSERT @T SELECT 't1','t2',78
UNION ALL SELECT 't1','t3',78
UNION ALL SELECT 't5','t6',72
SELECT cell=cell,bcchno=bcchno FROM @T
UNION ALL
SELECT cell=cellr,bcchno=bcchno FROM @Tcell bcchno
---- -----------
t1 78
t1 78
t5 72
t2 78
t3 78
t6 72(所影响的行数为 6 行)
t1
t1
t5
t2
t3
t6 我要的是
t1
t2
t1
t3
t5
t6
-- Author: liangCK 小梁
-- Date : 2008-11-15 19:10:51
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (cell VARCHAR(2),cellr VARCHAR(2),bcchno INT)
INSERT INTO @T
SELECT 't1','t2',78 UNION ALL
SELECT 't1','t3',78 UNION ALL
SELECT 't5','t6',72--SQL查询如下:;WITH Liang
AS
(
SELECT
cell,
cellr,
bcchno,
rid=ROW_NUMBER()
OVER(ORDER BY GETDATE())
FROM
@T
)
SELECT
cell,
bcchno
FROM
(
SELECT cell,bcchno,rid,id=0 FROM Liang
UNION ALL
SELECT cellr,bcchno,rid,id=1 FROM Liang
) AS T
ORDER BY
rid,
id/*
cell bcchno
---- -----------
t1 78
t2 78
t1 78
t3 78
t5 72
t6 72(6 行受影响)*/
那使用临时表.整个IDENTITY出来.然后像上面的就可以了.
(
id int identity(1,1),
cell nvarchar(20),
cellr nvarchar(20),
bcchno nvarchar(20)
)
insert into #AB select 't1','t2','78'
union all select 't1','t3','78'
union all select 't5','t6','72'select ID,cell,bcchno from #Ab
union all
select ID, cellr,bcchno from #Ab
ORDER BY ID,CELLdrop table #ab
/*
ID cell bcchno
----------- -------------------- --------------------
1 t1 78
1 t2 78
2 t1 78
2 t3 78
3 t5 72
3 t6 72(6 行受影响)*/
(
-- id int identity(1,1),
cell nvarchar(20),
cellr nvarchar(20),
bcchno nvarchar(20)
)
insert into #AB select 't1','t2','78'
union all select 't1','t3','78'
union all select 't5','t6','72'
--临时表
SELECT ID=identity(INT,1,1),* INTO #AA
FROM #ABselect ID,cell,bcchno from #AA
union all
select ID, cellr,bcchno from #AA
ORDER BY ID,CELL