select id,username from userTable
显示的效果是
id username
1 123
2 234
3 hell怎样才SQL中转换下变成
id 1,2,3
username 123,234,hell
牛人们,帮下忙吧,
显示的效果是
id username
1 123
2 234
3 hell怎样才SQL中转换下变成
id 1,2,3
username 123,234,hell
牛人们,帮下忙吧,
insert into tb values('A1','a','b','c','a,c','a,b,f')
insert into tb values('B1','c','d','e','f,e','c')
go
--1,生成一临时表
select id , i1 + ',' + i2 + ',' + i3 + ',' + i4 + ',' + i5 txt into tmp from tb
--2,建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
--3,获取数据
SELECT
A.ID,
I = SUBSTRING(A.txt, B.ID, CHARINDEX(',', A.txt + ',', B.ID) - B.ID)
FROM tmp A, # B
WHERE SUBSTRING(',' + a.txt, B.id, 1) = ','
ORDER BY 1,2
GO
--这里的1,2代表的事第一列和第二列的意思
drop table tb,tmp,#
select case when id='a1' then i1 end from tb
--select CHARINDEX(',', A.txt + ',', B.ID) - B.ID FROM tmp A, # B
/*
ID I
---------- ----
A1 a
A1 a
A1 a
A1 b
A1 b
A1 c
A1 c
A1 f
B1 c
B1 c
B1 d
B1 e
B1 e
B1 f
*/
declare @a varchar(5000),@b varchar(5000)
select @a='',@b=''
select @a=@a+','+rtrim(id),@b=@b+','+username from userTable
select 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'')
insert into tb values('1', '123')
insert into tb values('2', '234')
insert into tb values('3', 'hell')
go
declare @output1 varchar(8000)
declare @output2 varchar(8000)
select @output1 = coalesce(@output1+',', '') + id from tb
select @output2 = coalesce(@output2+',', '') + username from tb
print 'id ' + @output1
print 'username ' + @output2drop table tb/*
id 1,2,3
username 123,234,hell
*/