空的没必要显示吧?select a.表名,
stuff((select ','+rtrim(字段名称) from tb where 表名 =a.表名 for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
stuff((select ','+rtrim(字段名称) from tb where 表名 =a.表名 for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
结果非常接近。
厉害。。
stuff((select ','+rtrim(字段名称) from tb where 表名 =a.表名 for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
这个结果非常接近,还需要将空串加上。我主要是要这个对应的层次
DROP TABLE [table1]
CREATE TABLE [table1]
(
col varchar(100) NULL ,
tb varchar(100) NULL ,
id int NULL
)
GO--插入测试数据
INSERT INTO [table1] ([col],tb,[id])
SELECT 'field1','table1','1' UNION
SELECT 'field2','table1','2' UNION
SELECT 'field3','table1','3' UNION
SELECT 'field4','table2','4' UNION
SELECT 'field5','table2','5' UNION
SELECT 'field6','table2','6' UNION
SELECT 'field7','table3','7' UNION
SELECT 'field8','table3','8' UNION
SELECT 'field9','table4','9'
GOIF EXISTS (SELECT * FROM sysobjects WHERE NAME ='f_t')
DROP FUNCTION f_t
GOcreate function f_t(@tb VARCHAR(10))
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+ISNULL(NULLIF(a.col,(SELECT col FROM table1 WHERE col=a.col AND tb<>@tb)),'') from table1 a
return(stuff(@s,1,1,''))
end
GO--调用
SELECT tb,dbo.f_t(tb) FROM table1 GROUP BY tb--结果
/*
table1 field1,field2,field3,,,,,,
table2 ,,,field4,field5,field6,,,
table3 ,,,,,,field7,field8,
table4 ,,,,,,,,field9
*/
DROP TABLE [table1]
CREATE TABLE [table1]
(
col varchar(100) NULL ,
tb varchar(100) NULL ,
id int NULL
)
GO--插入测试数据
INSERT INTO [table1] ([col],tb,[id])
SELECT 'field1','table1','1' UNION
SELECT 'field2','table1','2' UNION
SELECT 'field3','table1','3' UNION
SELECT 'field4','table2','4' UNION
SELECT 'field5','table2','5' UNION
SELECT 'field6','table2','6' UNION
SELECT 'field7','table3','7' UNION
SELECT 'field8','table3','8' UNION
SELECT 'field9','table4','9'
GOIF EXISTS (SELECT * FROM sysobjects WHERE NAME ='f_t')
DROP FUNCTION f_t
GOcreate function f_t(@tb VARCHAR(10))
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+ISNULL(NULLIF(a.col,(SELECT col FROM table1 WHERE col=a.col AND tb<>@tb)),'''''') from table1 a
return(stuff(@s,1,1,''))
end
GO--调用
SELECT tb,dbo.f_t(tb) FROM table1 GROUP BY tb--结果
/*
table1 field1,field2,field3,'','','','','',''
table2 '','','',field4,field5,field6,'','',''
table3 '','','','','','',field7,field8,''
table4 '','','','','','','','',field9
*/
效果达到了。但是我不想用函数。能否
select a.表名,
stuff((select ','+rtrim(字段名称) from tb where 表名 =a.表名 for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
在这个基础上改进了?谢谢
if object_id('tempdb..#tb') is not null
drop table #tb
select * into #tb
from
(
select 'field1' as 字段名称, 'table1' as 表, '1' as 排序号
union all
select 'field2', 'table1', '2'
union all
select 'field3', 'table1', '3'
union all
select 'field4', 'table2', '4'
union all
select 'field5', 'table2', '5'
union all
select 'field6', 'table2', '6'
union all
select 'field7', 'table3', '7'
union all
select 'field8', 'table3', '8'
union all
select 'field9', 'table4', '9'
)tdeclare @field varchar(100),@sql varchar(2000)
set @field = stuff((select distinct ',' + 字段名称 from #tb for xml path('')),1,1,'')set @sql = '
select 表, ' + @field + '
from
(
select a.字段名称,t.表,a.字段名称 as 排序号 from #tb a
right join
(
select distinct 表,number From #tb ,master..spt_values
where type =''p''
and number between 1 and (select count(1) from #tb)
)t on a.表 = t.表 and a.排序号 = t.number
)t
pivot
(
max(排序号) for 字段名称 in (' + @field + ')
)p
'
print @sql
exec (@sql)/*
表 field1 field2 field3 field4 field5 field6 field7 field8 field9
------ ------ ------ ------ ------ ------ ------ ------ ------ ------
table1 field1 field2 field3 NULL NULL NULL NULL NULL NULL
table2 NULL NULL NULL field4 field5 field6 NULL NULL NULL
table3 NULL NULL NULL NULL NULL NULL field7 field8 NULL
table4 NULL NULL NULL NULL NULL NULL NULL NULL field9
*/
stuff((select ','+rtrim(字段名称) from tb where 表名 =a.表名 for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
在这个基础上改进了?谢谢
谁能帮我修改一下吗??达到这种效果:
--结果
/*
table1 field1,field2,field3,'','','','','',''
table2 '','','',field4,field5,field6,'','',''
table3 '','','','','','',field7,field8,''
table4 '','','','','','','','',field9
*/马上就结贴了
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='table1')
DROP TABLE [table1]
CREATE TABLE [table1]
(
col varchar(20) NULL ,
tb varchar(20) NULL ,
id int NULL
)
GO--插入测试数据
INSERT INTO [table1] ([col],tb,[id])
SELECT 'field1','table1','1' UNION
SELECT 'field2','table1','2' UNION
SELECT 'field3','table1','3' UNION
SELECT 'field4','table2','4' UNION
SELECT 'field5','table2','5' UNION
SELECT 'field6','table2','6' UNION
SELECT 'field7','table3','7' UNION
SELECT 'field8','table3','8' UNION
SELECT 'field9','table4','9'
GO
DECLARE @s varchar(8000)
SELECT @s='SELECT [tb] '
SELECT @s=@s+',MAX(CASE WHEN [col]='''+col+''' THEN col ELSE null END)as '''+col+''' '
FROM table1 GROUP BY col ORDER BY col
SELECT @s=@s+' FROM table1 GROUP BY tb '
EXEC(@s)/*(所影响的行数为 9 行)tb field1 field2 field3 field4 field5 field6 field7 field8 field9
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
table1 field1 field2 field3 NULL NULL NULL NULL NULL NULL
table2 NULL NULL NULL field4 field5 field6 NULL NULL NULL
table3 NULL NULL NULL NULL NULL NULL field7 field8 NULL
table4 NULL NULL NULL NULL NULL NULL NULL NULL field9警告: 聚合或其它 SET 操作消除了空值。
*/
stuff((select case when 表名<>a.表名 then ' '''' ' else ','+rtrim(字段名称) end from tb for xml path('')),1,1,'') as 字段名称
from tb a
group by a.表名
不好意思,没有按时结贴。马上结贴。谢谢各位。。牛人太多啦。
go
create table #tt
(
fieldName varchar(20),
tableName varchar(20),
sort int
)
go
insert into #tt select 'field1','table1',1
insert into #tt select 'field2','table1',2
insert into #tt select 'field3','table1',3
insert into #tt select 'field4','table2',4
insert into #tt select 'field5','table2',5
insert into #tt select 'field6','table2',6
insert into #tt select 'field7','table3',7
insert into #tt select 'field8','table3',8
insert into #tt select 'field9','table4',9
go
with tb as
(
select distinct tableName,fileInfo=replace((select fieldName as 'data()' from #tt where tableName=b.tableName for xml path('')),' ',',') from #tt b
)
select
Replicate(''''',',convert(int,substring(fileInfo,charindex(',',fileInfo+',')-1,1))-1)+fileInfo+Replicate(',''''',9-convert(int,right(fileInfo,1)))
from tb