sqlserver 先转行后转列

lily8669 2010-01-05 12:01:53
已经转成行了..现在想把行转成列...
转成行的SQL
select t.licName taName, t.licNo licNo, t.turn turn,t.year year
from(
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '01') as year, lmt.JAN as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '02') as year, lmt.FEB as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '03') as year, lmt.MAR as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '04') as year, lmt.APR as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '05') as year, lmt.MAY as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '06') as year, lmt.JUN as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '07') as year, lmt.JUL as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '08') as year, lmt.AUG as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '02') as year, lmt.FEB as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '10') as year, lmt.OCT as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '11') as year, lmt.NOV as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
union all
select (lic.E_LICENSEE +' '+ lic.C_LICENSEE)licName,lic.LIC_NO licNo,
(CAST(lmt.MAINLAND_YEAR AS varchar(20)) + '12') as year, lmt.DEC as turn
from LICENCE lic, LICENCE_MAINLAND_TOUR lmt
where lic.LIC_NO = lmt.LIC_NO
)t
where t.year >= 200501
and t.year <= 200812
order by t.licNo,t.year,t.turn
结果:
taName licNo turn year
111 222 2410001 10 200501
111 222 2410001 10 200502
111 222 2410001 10 200502
111 222 2410001 0 200503
111 222 2410001 NULL 200504
111 222 2410001 NULL 200505
111 222 2410001 NULL 200506
111 222 2410001 NULL 200507
111 222 2410001 NULL 200508
111 222 2410001 NULL 200510
111 222 2410001 NULL 200511
111 222 2410001 NULL 200512
111 222 2410001 NULL 200601
111 222 2410001 NULL 200602
111 222 2410001 NULL 200602
111 222 2410001 10 200603
111 222 2410001 NULL 200604
111 222 2410001 NULL 200605
111 222 2410001 NULL 200606
111 222 2410001 NULL 200607
111 222 2410001 NULL 200608
111 222 2410001 NULL 200610
111 222 2410001 NULL 200611
111 222 2410001 NULL 200612
111 222 2410001 NULL 200701
111 222 2410001 NULL 200702
111 222 2410001 NULL 200702
111 222 2410001 0 200703
111 222 2410001 NULL 200704
111 222 2410001 NULL 200705
111 222 2410001 NULL 200706
111 222 2410001 10 200707
111 222 2410001 NULL 200708
111 222 2410001 NULL 200710
111 222 2410001 NULL 200711
111 222 2410001 NULL 200712
aa aa 2410002 50 200501
aa aa 2410002 50 200502
aa aa 2410002 50 200502
aa aa 2410002 0 200503
aa aa 2410002 NULL 200504
aa aa 2410002 NULL 200505
aa aa 2410002 NULL 200506
aa aa 2410002 NULL 200507
aa aa 2410002 NULL 200508
aa aa 2410002 NULL 200510
aa aa 2410002 NULL 200511
aa aa 2410002 NULL 200512
aa aa 2410002 NULL 200601
aa aa 2410002 NULL 200602
aa aa 2410002 NULL 200602
aa aa 2410002 50 200603
aa aa 2410002 NULL 200604
aa aa 2410002 NULL 200605
aa aa 2410002 NULL 200606
aa aa 2410002 NULL 200607
aa aa 2410002 NULL 200608
aa aa 2410002 NULL 200610
aa aa 2410002 NULL 200611
aa aa 2410002 NULL 200612
aa aa 2410002 NULL 200701
aa aa 2410002 NULL 200702
aa aa 2410002 NULL 200702
aa aa 2410002 0 200703
aa aa 2410002 NULL 200704
aa aa 2410002 NULL 200705
aa aa 2410002 NULL 200706
aa aa 2410002 50 200707
aa aa 2410002 NULL 200708
aa aa 2410002 NULL 200710
aa aa 2410002 NULL 200711
aa aa 2410002 NULL 200712
B11 b11 2410003 NULL 200801
B11 b11 2410003 NULL 200802
B11 b11 2410003 NULL 200802
B11 b11 2410003 0 200803
B11 b11 2410003 10 200804
B11 b11 2410003 NULL 200805
B11 b11 2410003 NULL 200806
B11 b11 2410003 NULL 200807
B11 b11 2410003 NULL 200808
B11 b11 2410003 NULL 200810
B11 b11 2410003 NULL 200811
B11 b11 2410003 NULL 200812
333 444 2410004 NULL 200801
333 444 2410004 NULL 200802
333 444 2410004 NULL 200802
333 444 2410004 0 200803
333 444 2410004 50 200804
333 444 2410004 NULL 200805
333 444 2410004 NULL 200806
333 444 2410004 NULL 200807
333 444 2410004 NULL 200808
333 444 2410004 NULL 200810
333 444 2410004 NULL 200811
333 444 2410004 NULL 200812
现在想把结果转成列的形式
例如:
taName licNo 200501 200502...200712
111 222 2410001 10 10 ...NULL
...

分不是很多..请各位帮忙了...
...全文
58 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
nianran520 2010-01-05
  • 打赏
  • 举报
 回复 
/*

原表：news_pic 

id    news_id          pic_name            pic_shuoming 

12      228        ../upfile/10.jpg          haha.. 

13      30          ../upfile/15.jpg          happy 

18      30          ../upfile/25.jpg          来访客人 

20      1588        ../upfile/26.jpg          来访客人 

46      75          ../upfile/28.jpg          英国客人 

... 



转换： 

news_id    pic_name1          pic_shuoming1      pic_name2            pic_shuoming2  ... 

228      ../upfile/10.jpg      haha.. 

30        ../upfile/15.jpg      happy          ../upfile/25.jpg        来访客人 

20        ../upfile/26.jpg      来访客人 

46        ../upfile/28.jpg      英国客人 



*/

--> 测试数据：tb

if object_id('tb') is not null drop table tb

create table tb([id] int,[news_id] int,[pic_name] varchar(16),[pic_shuoming] varchar(8))

insert tb

select 12,228,'../upfile/10.jpg','haha..' union all

select 13,30,'../upfile/15.jpg','happy' union all

select 18,30,'../upfile/25.jpg','来访客人' union all

select 20,1588,'../upfile/26.jpg','来访客人' union all

select 46,75,'../upfile/28.jpg','英国客人'



select (select count(1) from tb where news_id = t.news_id and [id] <= t.[id]) as group_id,* 

into #temp from tb t



declare @sql varchar(8000)

select @sql = ''

select @sql = @sql + ',max(case group_id when '''+ltrim(group_id)+''' then pic_name else '''' end) as [pic_name'+ltrim(group_id)+'],

max(case group_id when '''+ltrim(group_id)+''' then pic_shuoming else '''' end) as [pic_shuoming'+ltrim(group_id)+']' 

from (select distinct group_id from #temp) t



select @sql = 'select news_id'+@sql+' from #temp group by news_id order by max([id])'



exec(@sql)



drop table #temp
lily8669 2010-01-05
  • 打赏
  • 举报
 回复
这种的都太简单了..

我的是从表里面先转成行..

之后从转成行的结果在转回列..
--小F-- 2010-01-05
  • 打赏
  • 举报
 回复 
、列转行

--> --> (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 行受影响)

*/
--小F-- 2010-01-05
  • 打赏
  • 举报
 回复
2005以上可以先用CTE
再列转行
lily8669 2010-01-05
  • 打赏
  • 举报
 回复
有没有具体例子..?
thx..
nianran520 2010-01-05
  • 打赏
  • 举报
 回复
把结果存成临时表
接下来的看精华帖
lily8669 2010-01-05
  • 打赏
  • 举报
 回复
学习了...

结贴..给分..
nianran520 2010-01-05
  • 打赏
  • 举报
 回复 
--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb]([taName] varchar(20),[licNo] varchar(20),[turn] int,[year] int)

insert [tb]

select '111 222',2410001,10,200501 union all

select '111 222',2410001,10,200502 union all

select '111 222',2410001,10,200502 union all

select '111 222',2410001,0,200503 union all

select '111 222',2410001,null,200504 union all

select '111 222',2410001,null,200505 union all

select 'aa aa',2410002,50,200501 union all

select 'aa aa',2410002,50,200502 union all

select 'aa aa',2410002,50,200502 union all

select 'aa aa',2410002,0,200503 union all

select 'aa aa',2410002,null,200504 union all

select 'aa aa',2410002,null,200505 union all

select 'aa aa',2410002,null,200506 union all

select 'aa aa',2410002,null,200507 union all

select 'aa aa',2410002,null,200508 union all

select 'B11 b11',2410003,null,200801 union all

select 'B11 b11',2410003,null,200802 union all

select 'B11 b11',2410003,null,200802 union all

select 'B11 b11',2410003,0,200803 union all

select 'B11 b11',2410003,10,200804 union all

select 'B11 b11',2410003,null,200805



declare @sql varchar(8000)



select @sql=isnull(@sql+',','')+'max(case year when '+ltrim(year)+' then turn else null end) as ['+ltrim(year)+']'

from (select distinct top 100 percent year from [tb] order by year) t



select @sql = 'select taName,licNo,'+@sql+' from [tb] group by taName,licNo'



exec(@sql)

------------------------------------

111 222	2410001	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

aa aa	2410002	50	50	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

B11 b11	2410003	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	10	NULL
lily8669 2010-01-05
  • 打赏
  • 举报
 回复
还是不行啊...
sqlserver 实现 行转列 split 分割的函数
sqlserver 实现 行转列 split 分割的函数，具体使用方法写有在文件里。
SqlServer行转列和列转行
SqlServer如何进行行转列和列转行方法
3天掌握SQL Server 2012 数据库
SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），Microsoft SQL Server 2012 是一个全面的数据库平台，使用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为...
SQL Server 列转行代码
SQL Server 列转行代码，供大伙参考
sql列转行以及行转列的通用存储过程
sql列转行以及行转列的通用存储过程！mssqlserver版本！
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章