SQL 行转列问题

[冰川711]

表结构


id name
1 公司名称
2 姓名
3 数量



结果

1 2 3
公司名称 姓名 数量

[liang145]

create table #tb
(id int, name nvarchar(20))
insert #tb
select 1, '公司名称' union all
select 2, '姓名' union all
select 3, '数量'


declare @sql as  nvarchar(4000)
set @sql=''
select @sql=@sql+' ,max(case id when '+ltrim(id)+' then name else '''' end) as '''+ltrim(id)+'''' 
        from (select distinct id from #tb) as t
set @sql='select '+stuff(@sql,1,2,'')+' from #tb'
exec(@sql)

[水向东流时光怎么偷]

select 
    [project],
    [多功能一体机]=max(case when [item]='多功能一体机' then [qty] else 0 end),
    [其他（多功能业务）]=max(case when [item]='其他（多功能业务）' then [qty] else 0 end),
    [激光打印机]=max(case when [item]='激光打印机' then [qty] else 0 end),
    [其他（激光业务）]=max(case when [item]='其他（激光业务）' then [qty] else 0 end),
    [喷墨打印机]=max(case when [item]='喷墨打印机' then [qty] else 0 end),
    [喷墨耗材]=max(case when [item]='喷墨耗材' then [qty] else 0 end),
    [其他（喷墨业务）]=max(case when [item]='其他（喷墨业务）' then [qty] else 0 end),
    [耗材]=max(case when [item]='耗材' then [qty] else 0 end),
    [外设数码]=max(case when [item]='外设数码' then [qty] else 0 end)
from 
    Periph
group by [project]

上面的语句也是一个行转例,你借鉴一吧

[水向东流时光怎么偷]

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')


生成静态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]

GO
动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78

(2 行受影响)
*/

------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)

--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/

[水向东流时光怎么偷]

将下表数据：
A b c d e   
-------------------- ----------- ----------- ----------- -----------  
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12

转化成如下结果：
a x y z   
-------------------- ---------- ---------- ----------  
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12

--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12


--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)

--借助中间表实现行列转换
declare @name varchar(20)

declare t_cursor cursor for  
select name from syscolumns  
where id=object_id('test1') and colid>1 order by colid

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status=0
begin
  exec('select '+@name+' as t into test3 from test1')
  set @s='insert into test2 select '''+@name+''''
  select @s=@s+','''+rtrim(t)+'''' from test3
  exec(@s)
  exec('drop table test3')
  fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor


--查看行列互换处理结果
select * from test1
select * from test2

--删除表
drop table test1
drop table test2

----------------------------------------------------------------------------
表1：项目种类 业绩 提成
  洗吹类　　200 10
  外卖 100 5
  合计 300 15
转换成:
  项目种类 洗吹类 外卖 合计
  业绩 200 100 300
  提成 10 5 15

if object_id('pubs..tb') is not null
  drop table tb
go

create table tb(
项目种类 varchar(10),
业绩 int,
提成 int
)

insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
go

select 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
select 项目种类 = '业绩',
 洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
 外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
 合计 = case when 项目种类 = '合计' then 业绩 else 0 end
from tb
union all
select 项目种类 = '提成' ,
 洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
 外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
 合计 = case when 项目种类 = '合计' then 提成 else 0 end
from tb
) m
group by 项目种类
order by 项目种类 desc

drop table tb

项目种类 洗吹类 外卖 合计   
---- ----------- ----------- -----------  
业绩 200 100 300
提成 10 5 15

（所影响的行数为 2 行）




数据库中有张表格如下
  
  工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330

我想得到的结果是

  1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330

就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？


if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO

/*--行列互换的通用存储过程 ： 将指定的表,按指定的字段进行行列互换

--邹建 2004.04--

--使用示例

--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2

--要求转换结果
/*
性别 小说 散文 哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
*/

--调用存储过程
exec p_zj '表','类别','性别'

--删除测试
drop table 表
*/

create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then ''
else @new_fdname+'=' end+''''''+name+''''''''
-- ,@s2=@s2+',@'+@i+'=''性别='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+['+@fdname+']+'']=''+cast(['+name+'] as varchar) from ['+@tbname+']'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname

select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go


--用上面的存储过程测试：

create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330

select * from Test
/*
月份工资福利奖金
1月100200300
2月110210310
3月120220320
4月130230330
*/

exec p_zj 'Test', '月份', '月份'
/*
月份1月2月3月4月
福利200210220230
工资100110120130
奖金300310320330
*/

drop table Test
--drop proc p_zj


静态写法

--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330

--测试语句
SELECT * FROM  
(
SELECT 考核月份,月份,金额
FROM  
  (SELECT 月份, 工资, 福利, 奖金
  FROM Test) p
UNPIVOT
  (金额 FOR 考核月份 IN  
  (工资, 福利, 奖金)
)AS unpvt
) T
 PIVOT
(MAX(金额)
 FOR 月份 in ([1月],[2月],[3月],[4月])
)AS pt

--测试结果

/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/

--删除环境
Drop table Test