SQL 动态行转列
现有一张【Hong_lvnew】表，
表中的列为【regtime,lv1,lv2,lv3 ,lv4 ,lv5 ,lv6, lv7, lv8,lv9 ,lv10, lv11,lv12 , lv13, lv14 ,lv15】
表中的数据为：
regtime lv1 lv2 lv3 lv4 lv5 lv6 lv7 lv8 lv9 lv10 lv11 lv12 lv13 lv14 lv15
2011-11-07 122 112 116 491 468753 692 187 922 439 421 247 668 632 119
2011-08-23 433 449 226 491 468753 692 187 922 439 421 247 668 632 119
2011-06-06 111 559 336 491 468753 692 187 922 439 421 247 668 632 119
2011-02-14 222 330 446 491 468753 692 187 922 439 421 247 668 632 119
2011-12-19 333 449 556 491 468753 692 187 922 439 421 247 668 632 119
.......
现在我需要得到的结果格式为：
分布等级 2011-11-07 2011-08-23 2011-06-06 2011-02-14 2011-12-19
lv1 122 433 111 222 333
lv2 112 449 559 330 449
lv3 116 226 336 446 556
lv4 491 491 491 491 491
lv5 468 468 468 468 468
lv6 753 753 753 753 753
lv7 692 922 922 922 922
..........
[解决办法]
Hong_lvnew 表中要是有上万的数据，结果中就要有上万的列？
SQL SERVER 2005的列数上限貌似是1024
[解决办法]
表的列是有限制的。
[解决办法]
参考这个,特别是后面那个存储过程.
/*
标题：90度旋转行列转换之一
作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
时间：2010-05-08
地点：重庆航天职业学院
说明：无
*/
/*
数据库中tb表格如下
月份 工资 福利 奖金
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 语句实现？
*/
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj @tbname sysname, --要处理的表名 @fdname sysname, --做为转换的列名 @new_fdname sysname='' --为转换后的列指定列名asdeclare @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 + '''''''', @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)+1from syscolumnswhere object_id(@tbname)=id and name<>@fdnameselect @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 allselect '2月',110,210,310 union allselect '3月',120,220,320 union allselect '4月',130,230,330go--用上面的存储过程测试：exec p_zj 'Test', '月份' , '项目'drop table Testdrop proc p_zj/*项目 1月 2月 3月 4月-------- ------ -------- -------- --------奖金 300 310 320 330工资 100 110 120 130福利 200 210 220 230（所影响的行数为 3 行）*/
[解决办法]
--表名tb
create table tc(regtime datetime,num int,flag varchar(20))
declare @sql varchar(max)
select @sql = isnull(@sql+' union all ','')+' select regtime,'+[name]+','''+[name]+''' flag from tb '
from sys.columns
where [object_id] = object_id('tb')
insert into tc exec(@sql)
set @sql = 'select flag,'
select @sql = @sql + ',sum(case when convert(varchar(10),regtime,120)='''+date+''' then num else 0 end) ['+date+']'
from (select convert(varchar(10),regtime,120) date from tc group by convert(varchar(10),regtime,120))t
select @sql = @sql + ' from tc group by flag '
exec(@sql)
drop table tc
[解决办法]
表a
name num
aa 1
bb 2
cc 4
dd 4
ee 2
一条语句实现如下:
name
aa,bb,cc,dd,ee
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T
SELECT @STR
问题描述：
无论是在sql 2000，还是在sql 2005 中,都没有提供字符串的聚合函数，
所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
1 aa,bb
2 aaa,bbb,ccc
即，group by id, 求value 的和（字符串相加）
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *FROM (SELECT DISTINCT Id FROM @t)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
( SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
GO
--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
GO
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
SELECT col1,col2=CAST(col2 as varchar(100))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1 col2
a 1
a 1,2
b 1
b 1,2
b 1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO
--3.3.4.1 每组<=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=1 THEN ''
ELSE ','+CAST(MAX(col2) as varchar)
END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ----------
a 1,2
b 1,2
c 3
--*/
--3.3.4.2 每组<=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3
--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
ELSE ''
END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ------------
a 1,2
b 1,2,3
c 3
--*/
GO
if not object_id('A') is null
drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> -->
if not object_id('B') is null
drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from B
id cname
1 张三,李四,王五
2 王五,蔡六
(2 個資料列受到影響)
我现在有一字段值为:a,b,c
分隔符为逗号.
我现在想实现目地值为:
字段:
id name
1 a
2 b
3 c
在SQL中咋实现呢?
declare @str varchar(8000)
set @str = 'a1,b1,c2,d1,e3,f5'
set @str = 'select name='''+replace(@str,',',''''+' union all select ''')+''''
set @str='select id=identity(int,1,1),name into #temp from ('+@str+') a select * from #temp drop table #temp'
exec(@str)
--参考:
CREATE TABLE TB(ID VARCHAR(6), COLOR NVARCHAR(30))
INSERT TB
SELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL
SELECT '173160', N'#特深蓝色,#特深蓝色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色' UNION ALL
SELECT '911169', N'#宝蓝色,#宝蓝色,#花灰色,#花灰色'
GO
CREATE FUNCTION F_getStr(@color nvarchar(30))
returns nvarchar(30)
as
begin
declare @str nvarchar(30),@temp nvarchar(30)
set @str=''
set @temp=''
while charindex(',', @color+',')>0
begin
set @temp=left(@color, charindex(',', @color+',')-1)
if charindex(','+@temp+',', ','+@str+',')=0
set @str=@str+','+@temp
set @color=stuff(@color, 1, charindex(',', @color+','), '')
end
return stuff(@str, 1, 1, '')
end
go
SELECT ID,dbo.F_getStr(COLOR) as COLOR FROM TB
DROP TABLE TB
DROP FUNCTION F_getStr
/*
ID COLOR
173160 #特深蓝色
173160 #特深蓝色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
911169 #宝蓝色,#花灰色
*/
[解决办法]
可以查查SQL SERVER的pivot函数，它的功能就是行列转换……可以试试
[解决办法]
如下面的测试代码：
create table tb(姓名 varchar(10), 课程 varchar(10), 分数 int)
insert into tb values('wan', 'china', 90);
insert into tb values('wan', 'math', 59);
insert into tb values('lei', 'English', 89);
insert into tb values('lei', 'math', 86);
insert into tb values('lei', 'English', 79);
insert into tb values('mao', 'math', 68);
insert into tb values('mao', 'English', 81);
select * from tb;
declare @sqlm varchar(1000)
set @sqlm = 'select 姓名'
@sqlm = @sqlm + ',max(case 课程 when '''+ 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct [课程] from tb) as a
set @sqlm = @sqlm+ ' from tb group by 姓名'
exec(@sqlm)
这是我原来写的，提供你参考
[解决办法]
create table tb(regtime datetime,lv1 int,lv2 int,lv3 int,lv4 int,lv5 int,lv6 int,lv7 int,
lv8 int,lv9 int,lv10 int,lv11 int,lv12 int,lv13 int,lv14 int,lv15 int)
insert into tb
select '2011-11-07', 122, 112, 116, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-08-23', 433, 449, 226, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-06-06', 111, 559, 336, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-02-14', 222, 330, 446, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-12-19', 333, 449, 556, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119
go
--表名tb
create table tc(regtime datetime,num int,flag varchar(20))
declare @sql varchar(max)
select @sql = isnull(@sql+' union all ','')+' select regtime,'+[name]+','''+[name]+''' flag from tb '
from sys.columns
where [object_id] = object_id('tb') and [name] <> 'regtime'
insert into tc exec(@sql)
set @sql = 'select flag'
select @sql = @sql + ',sum(case when convert(varchar(10),regtime,120)='''+date+''' then num else 0 end) ['+date+']'
from (select convert(varchar(10),regtime,120) date from tc group by convert(varchar(10),regtime,120))t
select @sql = @sql + ' from tc group by flag order by cast(replace(flag,''lv'','''') as int)'
exec(@sql)
drop table tc,tb
/**************************
flag 2011-02-14 2011-06-06 2011-08-23 2011-11-07 2011-12-19
-------------------- ----------- ----------- ----------- ----------- -----------
lv1 222 111 433 122 333
lv2 330 559 449 112 449
lv3 446 336 226 116 556
lv4 491 491 491 491 491
lv5 468 468 468 468 468
lv6 753 753 753 753 753
lv7 692 692 692 692 692
lv8 187 187 187 187 187
lv9 922 922 922 922 922
lv10 439 439 439 439 439
lv11 421 421 421 421 421
lv12 247 247 247 247 247
lv13 668 668 668 668 668
lv14 632 632 632 632 632
lv15 119 119 119 119 119
(15 行受影响)
[解决办法]
create table Hong_lvnew
(regtime date,lv1 int,lv2 int,lv3 int,lv4 int,lv5 int,lv6 int, lv7 int,
lv8 int,lv9 int,lv10 int, lv11 int,lv12 int, lv13 int, lv14 int,lv15 int)
insert into Hong_lvnew
select '2011-11-07', 122, 112, 116, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-08-23', 433, 449, 226, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-06-06', 111, 559, 336, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-02-14', 222, 330, 446, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119 union all
select '2011-12-19', 333, 449, 556, 491, 468, 753, 692, 187, 922, 439, 421, 247, 668, 632, 119
declare @sql varchar(6000)
select @sql='with t as
(select cast(cc as varchar(8)) cc,regtime,val
from Hong_lvnew a
unpivot
(val for cc IN (lv1,lv2,lv3,lv4,lv5,lv6,lv7,lv8,lv9,lv10,lv11,lv12,lv13,lv14,lv15)) b
)
select cc,'
select @sql=@sql+'sum(case when regtime='''+cast(regtime as varchar(10))+''' then val else 0 end) ['+cast(regtime as varchar(10))+'],'
from Hong_lvnew
select @sql=left(@sql,len(@sql)-1)+'from t
group by cc
order by cast(substring(cc,charindex(''v'',cc)+1,4) as int)'
exec(@sql)
cc 2011-11-07 2011-08-23 2011-06-06 2011-02-14 2011-12-19
-------- ----------- ----------- ----------- ----------- -----------
lv1 122 433 111 222 333
lv2 112 449 559 330 449
lv3 116 226 336 446 556
lv4 491 491 491 491 491
lv5 468 468 468 468 468
lv6 753 753 753 753 753
lv7 692 692 692 692 692
lv8 187 187 187 187 187
lv9 922 922 922 922 922
lv10 439 439 439 439 439
lv11 421 421 421 421 421
lv12 247 247 247 247 247
lv13 668 668 668 668 668
lv14 632 632 632 632 632
lv15 119 119 119 119 119
(15 row(s) affected)
[解决办法]