我想把如下行转换为列怎么转换
1
2
3
4
5
转换为
1 2 3 4 5
而且不固定个数,就是不一定是5个最多到20
可能我没表达清楚,是转换为行,然后是5列,不是一个字符串,是下面这样
col1 col2 col3 col4 col5
1 2 3 4 5 不是
col1
1 2 3 4 5 如下答案不能达到要求的
declare @s varchar(1000)
select @s=isnull(@s+' ','')+ltrim(col) from tb
select @s
1
2
3
4
5
转换为
1 2 3 4 5
而且不固定个数,就是不一定是5个最多到20
可能我没表达清楚,是转换为行,然后是5列,不是一个字符串,是下面这样
col1 col2 col3 col4 col5
1 2 3 4 5 不是
col1
1 2 3 4 5 如下答案不能达到要求的
declare @s varchar(1000)
select @s=isnull(@s+' ','')+ltrim(col) from tb
select @s
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')+'max(case when col='+ltrim(col)+' then '+ltrim(col)+' else 0 end) as [col'+ltrim(col)+']'
from tb
exec ('select '+@sql+' from tb')--测试结果:
/*
col1 col2 col3 col4 col5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5(1 行受影响)
*/
if object_id('tb') is not null drop table tb
go
create table tb(col int)
insert tb select
21 union all select
22 union all select
23 union all select
24 union all select
25 union all select
26 union all select
27
select 1 as c1,col,id=identity(int,1,1) into #t from tb declare @s varchar(4000)select @s=isnull(@s+',','')+'max(case when col='+ltrim(col)+' then col else 0 end) ['+'col'+ltrim(id)+']'
from #tset @s='select c1,'+@s+' from #t group by c1'
exec(@s)
c1 col1 col2 col3 col4 col5 col6 col7
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 21 22 23 24 25 26 27(1 行受影响)
insert into tb values(1)
insert into tb values(2)
insert into tb values(3 )
insert into tb values(4 )
insert into tb values(5 )
godeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case val when ''' + cast(val as varchar) + ''' then val else 0 end) [' + cast(val as varchar) + ']'
from (select distinct val from tb) as a
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from (select id = 1 , val from tb)t group by id'
exec(@sql) drop table tb/*
1 2 3 4 5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
*/
Create Table Company
(
ComID varchar(50) primary key,
ComName nvarchar(50) not null,
ComNumber varchar(50) not null,
ComAddress nvarchar(200),
ComTele varchar(50)
)
--Create Product Table
Create Table Product
(
ProductID varchar(50) primary key,
ComID varchar(50) not null,
ProName nvarchar(200) not null,
ProNumber int not null
)
select * from product
--insert into table value
insert Company select '58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','CompanyOne','SH19991028','ShangHai','98765432112'
union all select '768B2E84-0AAB-4653-8F5B-5EF6165204DB','CompanyTwo','SH19991028','ShangHai','98765432113'
union all select 'AAE86C36-C82B-421D-BC55-E72368B1DE00','CompanyThree','SH19991028','ShangHai','98765432114'
union all select 'C672B359-C800-47DE-9BB4-6D0FC614594C','CompanyFour','SH19991028','ShangHai','98765432115'
union all select 'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','CompanyFive','SH19991028','ShangHai','98765432116'insert Product sleect('1598A60B-FCFD-4269-864B-CB999E8EA5CA','AAE86C36-C82B-421D-BC55-E72368B1DE00','SqlServer2005',500
union all select '19D7BF2F-79FD-414E-B648-F105D4AB1EBB' ,'AAE86C36-C82B-421D-BC55-E72368B1DE00', 'Office', 400
union all select '232B6109-C575-4316-A9BD-0C58F737BE7B' ,'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE', 'SqlServer2005' ,200
union all select '4F30E12C-7654-40CC-8245-DF1C3453FBC5' ,'768B2E84-0AAB-4653-8F5B-5EF6165204DB', 'Office', 400
union all select '54C6E4C2-1588-43DF-B22C-0697A1E27DB0' ,'58C0F3FD-7B98-4E74-A1A8-7B144FCB8707', 'Office', 400
union all select '551EB6CA-3619-4250-98A0-7231BB4C3D58' ,'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE', 'SqlServer2000', 100
union all select '5BAD331C-B6E4-440E-AC54-52CE13166843' ,'768B2E84-0AAB-4653-8F5B-5EF6165204DB', 'SqlServer2005', 1000
union all select '5C039C53-2EE4-4D90-BA78-7A20CEC4935C' ,'58C0F3FD-7B98-4E74-A1A8-7B144FCB8707', 'Windows2000', 200
union all select '673A8683-CD03-40D2-9DB1-1ADA812016E2' ,'58C0F3FD-7B98-4E74-A1A8-7B144FCB8707', 'WindowsXP', 100
union all select '6B9F771B-46EA-4496-B1DA-F10CB53F6F62' ,'C672B359-C800-47DE-9BB4-6D0FC614594C', 'WindowsXP', 100
union all select '770089B1-A80A-4F48-8537-E15BD00A99E7' ,'AAE86C36-C82B-421D-BC55-E72368B1DE00', 'WindowsXP', 100
union all select '92EED635-5C61-468A-B19D-01AAC112D8A3' ,'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE', 'SysBase', 100
union all select '99195297-F7F0-4DCD-964E-CFB8A162B6D0' ,'768B2E84-0AAB-4653-8F5B-5EF6165204DB', 'Windows2008', 300
union all select '9EF017C1-F8F0-49BC-A7BD-4DFFB6EA8037' ,'768B2E84-0AAB-4653-8F5B-5EF6165204DB', 'Windows2000', 200
union all select 'A31BCD44-7856-461F-A0FD-407DCA96E8A9' ,'C672B359-C800-47DE-9BB4-6D0FC614594C', 'SqlServer2005', 100
union all select 'A9B52E8F-129F-4113-A473-D4BDD2B3C09C' ,'768B2E84-0AAB-4653-8F5B-5EF6165204DB', 'WindowsXP' ,100
union all select 'AC228CA0-490C-4B3D-866D-154E771B2083' ,'58C0F3FD-7B98-4E74-A1A8-7B144FCB8707', 'Windows2008', 300
union all select 'BD0BA1D3-D1D2-4BC7-9EFD-78B1165060A0' ,'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE', 'DB2', 200
union all select 'CAA71AEA-7130-4AB8-955E-B04EA35A178A' ,'FDBA8B3F-1851-4B73-9A20-A24AEF721AAE', 'Oracle', 100--This is Business pack .
--Using this function can using table's row
--to new table's column
declare @strSql varchar(1000)
declare @column varchar(50)
declare @columns varchar(200)
set @columns = ''
/*According to Cursor get new table column*/
declare varchar_cur cursor for
select distinct proname from product order by proname
open varchar_cur
fetch next from varchar_cur into @column
while @@fetch_status = 0
begin
set @columns = @columns + '[' + @column + '],'
fetch next from varchar_cur into @column
end
Close varchar_cur
Deallocate varchar_cur
/*Converted to the ranks of the use of pivot*/
set @columns = stuff(@columns,len(@columns),1,'')
set @strSql = 'select comname,' + @columns
set @strSql = @strSql + ' from '
set @strSql = @strSql + ' ('
set @strSql = @strSql + ' select comname,pronumber,proname from product'
set @strSql = @strSql + ' left join company on product.comid = company.comid '
set @strSql = @strSql + ' ) as temp'
set @strSql = @strSql + ' pivot '
set @strSql = @strSql + ' ( '
set @strSql = @strSql + ' sum(pronumber) '
set @strSql = @strSql + ' for proname in (' + @columns + ') '
set @strSql = @strSql + ' ) as Pivot_table'exec(@strSql)