表 table1 字段k1,k2是primary keyk1 k2
1 1
2 1
2 2表 table2 字段k1,k2,k3是primary key
其中k1,k2对应table1的k1,k2
k1 k2 k3 kname
1 1 1 产品名1
1 1 2 产品名2
1 1 3 产品名3
2 1 1 产品名4
2 2 1 产品名2
2 2 2 产品名100一条sql语句将kname的行数据转为列数据
得到如下结果
k1 k2 kanme1 kanme2 kanme1 ......
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100
1 1
2 1
2 2表 table2 字段k1,k2,k3是primary key
其中k1,k2对应table1的k1,k2
k1 k2 k3 kname
1 1 1 产品名1
1 1 2 产品名2
1 1 3 产品名3
2 1 1 产品名4
2 2 1 产品名2
2 2 2 产品名100一条sql语句将kname的行数据转为列数据
得到如下结果
k1 k2 kanme1 kanme2 kanme1 ......
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100
select k1,k2,kanme as kname1, '' as kanme2,'' as kanme3 from tb where k3=1
union all
select k1,k2,'', kanme,'' from tb where k3=2
union all
select k1,k2,'', '',kname from tb where k3=3
go
create table [tb]([k1] int,[k2] int,[k3] int,[kname] varchar(9))
insert [tb]
select 1,1,1,'产品名1' union all
select 1,1,2,'产品名2' union all
select 1,1,3,'产品名3' union all
select 2,1,1,'产品名4' union all
select 2,2,1,'产品名2' union all
select 2,2,2,'产品名100'select k1,k2,max(kname1) as kname1,max(kname2) as kname2,max(kname3) as kname3
from(
select k1,k2,kname as kname1, '' as kname2,'' as kname3 from tb where k3=1
union all
select k1,k2,'', kname,'' from tb where k3=2
union all
select k1,k2,'', '',kname from tb where k3=3
) t
group by k1,k2/**
k1 k2 kname1 kname2 kname3
----------- ----------- --------- --------- ---------
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100 (3 行受影响)
**/
发现有一个限制,就是行数据的值必须固定,
可我这个行数据的产品名1....完全不固定。
select k1,k2,
max(case k3 when 1 then kname else '' end) as kname1,
max(case k3 when 2 then kname else '' end) as kname2,
max(case k3 when 3 then kname else '' end) as kname3
from tb
group by k1,k2/**
k1 k2 kname1 kname2 kname3
----------- ----------- --------- --------- ---------
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100 (3 行受影响)
**/
[产品名1]到[产品名100]甚至到[产品名100000]都有可能
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when k3='+ltrim(k3)+' then kname else '''' end) as [kname'+ltrim(k3)+']'
from (select distinct k3 from tb) t
set @sql='select k1,k2,'+@sql+'from tb group by k1,k2'
exec (@sql)/**
k1 k2 kname1 kname2 kname3
----------- ----------- --------- --------- ---------
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100 (3 行受影响)
**/
select @sql=isnull(@sql+',','')+'max(case when b.k3='+ltrim(b.k3)+' then b.kname else '''' end) as [kname'+ltrim(b.k3)+']'
from (select distinct k3 from b) t
set @sql='select a.k1,a.k2,'+@sql+'from a,b where a.k1=b.k1 and a.k2=b.k2 group by a.k1,a.k2'
exec(@sql)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-15 13:46:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([k1] int,[k2] int)
insert [a]
select 1,1 union all
select 2,1 union all
select 2,2
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([k1] int,[k2] int,[k3] int,[kname] varchar(9))
insert [b]
select 1,1,1,'产品名1' union all
select 1,1,2,'产品名2' union all
select 1,1,3,'产品名3' union all
select 2,1,1,'产品名4' union all
select 2,2,1,'产品名2' union all
select 2,2,2,'产品名100'
--------------开始查询--------------------------
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when k3='+ltrim(k3)+' then kname else '''' end) as [kname'+ltrim(k3)+']'
from (select distinct k3 from b) t
set @sql='select a.k1,a.k2,'+@sql+'from a,b where a.k1=b.k1 and a.k2=b.k2 group by a.k1,a.k2'
exec (@sql)----------------结果----------------------------
/* k1 k2 kname1 kname2 kname3
----------- ----------- --------- --------- ---------
1 1 产品名1 产品名2 产品名3
2 1 产品名4
2 2 产品名2 产品名100 (3 行受影响)
*/