单行数据变多行
创建测试表并插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
IF OBJECT_ID('mutitb') >0 DROP TABLE mutitb CREATE TABLE [dbo].[mutitb]( [id] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [VARCHAR](10) NULL, [LastName] [VARCHAR](10) NULL, CONSTRAINT [PK_mutitb] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT dbo.mutitb ( FirstName, LastName ) VALUES ( 'Anna', -- FirstName - varchar(10) 'Gates' -- LastName - varchar(10) ) INSERT dbo.mutitb ( FirstName, LastName ) VALUES ( 'John', -- FirstName - varchar(10) 'Doe' -- LastName - varchar(10) ) INSERT dbo.mutitb ( FirstName, LastName ) VALUES ( 'Joe', -- FirstName - varchar(10) 'Bloggs' -- LastName - varchar(10) ) INSERT dbo.mutitb ( FirstName, LastName ) VALUES ( 'Raj', -- FirstName - varchar(10) 'Kumar' -- LastName - varchar(10) )
1.Cross Join
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE #temp1 ( item VARCHAR(10) , itemdescription VARCHAR(20) ) INSERT #temp1 ( item, itemdescription ) VALUES ( '1', -- item - varchar(10) 'item1' -- itemdescription - varchar(20) ) INSERT #temp1 ( item, itemdescription ) VALUES ( '2', -- item - varchar(10) 'item2' -- itemdescription - varchar(20) ) INSERT #temp1 ( item, itemdescription ) VALUES ( '3', -- item - varchar(10) 'item3' -- itemdescription - varchar(20) ) SELECT * FROM mutitb CROSS JOIN #temp1 ORDER BY firstName DROP TABLE #temp1
2.Union all
3.with
顶
收藏
关注
评论
作者:王思明
出处:http://www.cnblogs.com/maanshancss/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。所有源码遵循Apache协议,使用必须添加 from maanshancss
出处:http://www.cnblogs.com/maanshancss/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。所有源码遵循Apache协议,使用必须添加 from maanshancss