新手,求解答表A:STUDENT
STUDENTID,STUDENTNAME
1, 'BOY A'
2, 'BOY B'表B:SUBJECT
STUDENTID,SUBJECT
1, '语文'
1, '数学'
1, '英语'
1, '化学
1, '物理'
1, '政治'
1, '体育'
2, '语文'要求用一条SQL语句得出一下结果STUDENTNAME SUBJECTS
'BOY A' '语文,数学,英语...'
'BOY B' '语文'谢谢
STUDENTID,STUDENTNAME
1, 'BOY A'
2, 'BOY B'表B:SUBJECT
STUDENTID,SUBJECT
1, '语文'
1, '数学'
1, '英语'
1, '化学
1, '物理'
1, '政治'
1, '体育'
2, '语文'要求用一条SQL语句得出一下结果STUDENTNAME SUBJECTS
'BOY A' '语文,数学,英语...'
'BOY B' '语文'谢谢
解决方案 »
- excel表导入ms sql时报错[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给
- 怎样删除这些记录
- 求SQL Server2000实时双机备份实际应用的最佳方案
- !!!!!急!!!!最简单的常识性问题
- 企业管理器里如何不显示系统表或是存储过程?
- sqlserver2008的连接问题(maximum number of '1' )
- 困扰了我两天我问题
- 外行问问题: 关于在SQLServer里面实现自反关系及删除字段
- 高手来,求复杂存储过程
- 如何从备份文件中取出备份路径
- insert时union怎么使用?
- 如何用T-SQL查询一张表中间的数据
无论是在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 行受影响)
--*/CSDN 社区帖子地址 附: 合并与分拆的CLR, sql2005的示例中有:
在安装sql 2005的示例后,默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
SELECT
A.STUDENTNAME,
B.SUBJECTS
FROM STUDENT A
CROSS APPLY(
SELECT SUBJECTS = STUFF(
(
SELECT
[*] = N', ' + SUBJECTS
FROM SUBJECT
WHERE A.STUDENTID = STUDENTID
FOR XML PATH(''), TYPE
).value('/', 'nvarchar(max)'),
1, 2, N'')
)B
无论是在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 Bid cname
----------- ----------------------------------------------------------------------------------------------------
1 张三,李四,王五
2 王五,蔡六(2 個資料列受到影響)
/******************************************************************************************************************************************************
合并分拆表数据整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go合并表:SQL2000用函数:go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from TabgoSQL2005用XML:方法1:select
a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct COl1 from Tab) a
Cross apply
(select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b方法2:select
a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query('<Tab>
{for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
{concat("",string(/Tab[last()]/@COl2))}
</Tab>')
)bSQL05用CTE:;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1
union all
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f(3 行受影响)
*/
拆分表:--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
GoSQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
SQL2005用Xml:select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
INSERT STUDENT
SELECT 1, 'BOY A' UNION ALL
SELECT 2, 'BOY B'CREATE TABLE SUBJECT(STUDENTID INT ,SUBJECT NVARCHAR(10))
INSERT SUBJECT
SELECT 1, N'语文' UNION ALL
SELECT 1, N'数学' UNION ALL
SELECT 1, N'英语' UNION ALL
SELECT 1, N'化学' UNION ALL
SELECT 1, N'物理' UNION ALL
SELECT 1, N'政治' UNION ALL
SELECT 1, N'体育' UNION ALL
SELECT 2, N'语文' GOCREATE FUNCTION F_GET_SUBJECT(@STUID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @SUBJECT NVARCHAR(100)
SET @SUBJECT=''
SELECT @SUBJECT=@SUBJECT+','''+SUBJECT+'''' FROM SUBJECT WHERE STUDENTID=@STUID
RETURN STUFF(@SUBJECT,1,1,'')
END
GOSELECT S.STUDENTID,STUDENTNAME,SUBJECT=dbo.F_GET_SUBJECT(S.STUDENTID) FROM STUDENT AS S JOIN SUBJECT AS J ON S.STUDENTID=J.STUDENTID GROUP BY S.STUDENTID,STUDENTNAME
DROP TABLE STUDENT
DROP TABLE SUBJECT
DROP FUNCTION F_GET_SUBJECT
/*
STUDENTID STUDENTNAME SUBJECT
----------- ----------- ----------------------------------------------------------------------------------------------------
1 BOY A '语文','数学','英语','化学','物理','政治','体育'
2 BOY B '语文'
*/
create table STUDENT(STUDENTID int ,STUDENTNAME varchar(10))
insert STUDENT select 1, 'BOY A'
insert STUDENT select 2, 'BOY B'
create table SUBJECT (STUDENTID int ,SUBJECT nvarchar(10))
insert SUBJECT SELECT 1, N'语文'
insert SUBJECT SELECT 1, N'数学'
insert SUBJECT SELECT 1, N'英语'
insert SUBJECT SELECT 1, N'化学'
insert SUBJECT SELECT 1, N'物理'
insert SUBJECT SELECT 1, N'政治'
insert SUBJECT SELECT 1, N'体育'
insert SUBJECT SELECT 2, N'语文'
GO
CREATE FUNCTION dbo.f_str(@id int)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @r nvarchar(4000)
SET @r = ''
SELECT @r = @r + ',' + SUBJECT
FROM SUBJECT
WHERE STUDENTID=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
SELECT a.STUDENTNAME, SUBJECTS=dbo.f_str(b.STUDENTID) FROM SUBJECT b,student a where a.STUDENTID=b.STUDENTID GROUP BY b.STUDENTID,a.STUDENTNAME
drop function dbo.f_str
drop table student
drop table subject
/*
STUDENTNAME SUBJECTS
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BOY A 语文,数学,英语,化学,物理,政治,体育
BOY B 语文*/
INSERT INTO Student
SELECT 1,'BOY A' UNION ALL
SELECT 2,'BOY B'CREATE TABLE SubJect(sID INT,sJect NVARCHAR(10))
INSERT INTO SubJect
SELECT 1,'语文' UNION ALL
SELECT 1,'数学' UNION ALL
SELECT 1,'英语' UNION ALL
SELECT 1,'化学' UNION ALL
SELECT 1,'物理' UNION ALL
SELECT 1,'政治' UNION ALL
SELECT 2,'数学' UNION ALL
SELECT 2,'语文' ALTER FUNCTION dbo.GetColumnInfo(@sID INT )
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @s NVARCHAR(1000)
SET @s=''
SELECT @s=@s+','+ sJect FROM SubJect WHERE sID=@sID
RETURN STUFF(@s,1,1,'')
END SELECT sID,sName,dbo.GetColumnInfo(sID)as 科目
FROM Student sID sName 科目
1 BOY A 语文,数学,英语,化学,物理,政治
2 BOY B 数学,语文(2 row(s) affected)
!!!!!!!!!!!!!!!!
zeyaseoluntanpalm
玖龙纸业
马明哲
中国平安
张茵
玖龙
玖龙纸业
张茵
玖龙纸业
龙大集团
eface
eface视觉娱乐
打标机
标记机
打号机
打码机
气动打标机
金属打标机
工业打标机
金属刻字机
毕业论文
代写论文
容灾
备份
数据库复制
扁桃体发炎
http://hi.baidu.com/mysiblog
北京市政府的机房,双向BGP出口带宽,100M 16万/年 全包,北方工大内,www.cio360.net 大家看看这个站的速度就知道机房速度。机房可根据用户需要修改路由,您或者您的朋友里有需要的联系我! 谢谢。曹丽娜
Mobile:13520756808
Tel: (86-10) 62684645/4542-8018
select studentid,
case SUBJECT WHEN '语文' THEN SUBJECT end as data1,
case SUBJECT WHEN '数学' THEN SUBJECT end as data2,
case SUBJECT WHEN '英语' THEN SUBJECT end as data3,
case SUBJECT WHEN '化学' THEN SUBJECT end as data4,
case SUBJECT WHEN '物理' THEN SUBJECT end as data5,
case SUBJECT WHEN '政治' THEN SUBJECT end as data6,
case SUBJECT WHEN '体育' THEN SUBJECT end as data7
from SUBJECT) group by studentid如果是在ORCALE数据库,如上一条语句即可解决问题,
1、纵表变横表
select studentid,
case SUBJECT WHEN '语文' THEN SUBJECT end as data1,
case SUBJECT WHEN '数学' THEN SUBJECT end as data2,
case SUBJECT WHEN '英语' THEN SUBJECT end as data3,
case SUBJECT WHEN '化学' THEN SUBJECT end as data4,
case SUBJECT WHEN '物理' THEN SUBJECT end as data5,
case SUBJECT WHEN '政治' THEN SUBJECT end as data6,
case SUBJECT WHEN '体育' THEN SUBJECT end as data7
from SUBJECT
2、合并个字段
查询时再根据id查出来
我越来越来喜欢上CSDN了
--处理的数据
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 Bid cname
----------- ----------------------------------------------------------------------------------------------------
1 张三,李四,王五
2 王五,蔡六(2 個資料列受到影響)
修改 删除 举报 引用 回复
加为好友
发送私信
在线聊天
roy_88
中国风
等级:
可用分等级:中农
总技术分:75353
总技术分排名:88
2
4
发表于:2008-10-20 10:30:364楼 得分:10
SQL code--合并分拆表
/******************************************************************************************************************************************************
合并分拆表数据整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go合并表:SQL2000用函数:go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from TabgoSQL2005用XML:方法1:
我是菜鸟,不知道这种函数可行否
合并字符串的功能。以前见到类似的帖子,先~
'语文' '数学' '英语' '化学 '物理' '政治' '体育' 如果能确认功课就这么多门,那简单多了,不用横纵转换也最多弄个临时表就出来了
如果不能,那就难度大了先创建个临时表temp1
insert into temp1 (ID, STUDENTNAME, subject) (select ID, STUDENTNAME , '' from STUDENT);
update temp1 set temp1.subject = '语文' from temp1, subject where temp1.id = subject.ID and subject.subject = '语文';
update temp1 set temp1.subject = case temp1.subject when '' then '数学' else temp1.subject + ',数学' end from temp1, subject where temp1.id = subject.ID and subject.subject = '语文';
update .......
反正一门功能执行一条sql就行了,最后再从temp1里select就OK
select student.studentname, substr(a.subject + b.subject +...., 1, len(a.subjet + b.subject + ....)-1) from student
left join (select studentid, subject + ',' as subject from subject where subject = '语文') a on student.id = a.studentid
left join (select studentid, subject + ',' as subject from subject where subject = '数学') b on student.id = b.studentid
.....大概就这么写吧,没测试,语法什么的可能有些不对
--处理的数据
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 個資料列受到影響) 修改 删除 举报 引用 回复 加为好友
发送私信
在线聊天
roy_88
中国风
等级:
可用分等级:中农
总技术分:75353
总技术分排名:88
2
4
发表于:2008-10-20 10:30:364楼 得分:10
SQL code--合并分拆表
/******************************************************************************************************************************************************
合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go 合并表: SQL2000用函数: go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法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 '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 個資料列受到影響)
修改 删除 举报 引用 回复 加为好友
发送私信
在线聊天
roy_88
中国风
等级:
可用分等级:中农
总技术分:75353
总技术分排名:88
2
4
发表于:2008-10-20 10:30:364楼 得分:10
SQL code--合并分拆表
/******************************************************************************************************************************************************
合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go 合并表: SQL2000用函数: go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: