--- 数据
DECLARE @A TABLE (Num INT ,column_1 CHAR(10), column_2 CHAR(10))INSERT INTO @A
SELECT 1 ,'AAA', 'Test1'
UNION ALL
SELECT 1,'CCC', 'Test2'
UNION ALL
SELECT 2 ,'BBB' ,'Test1'SELECT * FROM @A
--- 结果1 AAA Test1 CCC Test2
2 BBB Test1 NULL NULL
调试欢乐多
--- 数据
DECLARE @A TABLE (Num INT ,column_1 CHAR(10), column_2 CHAR(10))INSERT INTO @A
SELECT 1 ,'AAA', 'Test1'
UNION ALL
SELECT 1,'CCC', 'Test2'
UNION ALL
SELECT 2 ,'BBB' ,'Test1'
;
with cte as
(
SELECT row_number() over (partition by Num order by column_1 ) id ,Num ,column_1, column_2 FROM @A
)select t.Num,t.column_1,t.column_2,m.column_1,m.column_2 from
(select *from cte where id=1 ) as t left join
(select *from cte where id=2 ) as m
on t.Num=m.Num--- 结果--1 AAA Test1 CCC Test2
--2 BBB Test1 NULL NULL
能否动态实现了? column_1 , column_2 的值是不固定的 column_2 还有可能为别的值 如 Test3
DECLARE @A TABLE (Num INT ,column_1 CHAR(10), column_2 CHAR(10))INSERT INTO @A
SELECT 1 ,'AAA', 'Test1' UNION ALL
SELECT 1,'CCC', 'Test2' UNION ALL
SELECT 2 ,'BBB' ,'Test1'
SELECT IDENTITY(INT,1,1) AS id,column_1,column_2 INTO #temp FROM @ASELECT * FROM #temp
SELECT a.column_1,a.column_2,b.column_1,b.column_2 FROM #temp a
left JOIN #temp b ON a.id=b.id-1
WHERE a.id
SELECT a.column_1,a.column_2,b.column_1,b.column_2 FROM #temp a
left JOIN #temp b ON a.id=b.id-1
SELECT 1 ,'AAA', 'Test1' UNION ALL
SELECT 1,'CCC', 'Test2' UNION ALL
SELECT 2 ,'BBB' ,'Test1' UNION ALL
SELECT 2 ,'ddd' ,'Testee'SELECT IDENTITY(INT,1,1) AS id,column_1,column_2 INTO #temp FROM @ASELECT a.column_1,a.column_2,b.column_1,b.column_2 FROM #temp a
left JOIN #temp b ON a.id=b.id-1
WHERE (a.id%2)=1
DROP TABLE #temp
朋友,如果有多列的话
你可以写成动态的 sql 语句执行。
我上面的这个sql ,就可以动态的。
如何实现了?