22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TB(xm VARCHAR(2), zd VARCHAR(3), bds VARCHAR(20))
INSERT TB
SELECT 'A1', 'Zd1' , ''UNION ALL
SELECT 'A2', 'Zd2' , '' UNION ALL
SELECT 'A3', 'Zd3' , '' UNION ALL
SELECT 'A4', 'Zd4', 'A1/2' UNION ALL
SELECT 'A5', 'Zd5', 'A1/2' UNION ALL
SELECT 'A6', 'Zd6' , '' UNION ALL
SELECT 'A7', 'Zd7' , '' UNION ALL
SELECT 'A8', 'Zd8', 'A4+A5+A6+A7'
GO
CREATE FUNCTION F_GetField(@STR VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @SQL VARCHAR(20)
SET @SQL=@STR
SELECT @SQL=STUFF(@SQL,
CHARINDEX(XM,@SQL),
LEN(XM),
ZD) -- SELECT *
FROM TB WHERE CHARINDEX(XM,@SQL)>0
RETURN @SQL
END
GO
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+CASE WHEN bds<>'' THEN ZD+'=('+DBO.F_GetField(BDS)+')' ELSE zd END
FROM TB
SET @SQL=STUFF(@SQL,1,1,'')
PRINT @SQL
DROP TABLE TB
DROP FUNCTION F_GetField
/*
Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)
*/
DECLARE @t TABLE([xm] NVARCHAR(2),[zd] NVARCHAR(3),[bds] NVARCHAR(11))
INSERT @t SELECT N'A1',N'Zd1',NULL
UNION ALL SELECT N'A2',N'Zd2',NULL
UNION ALL SELECT N'A3',N'Zd3',NULL
UNION ALL SELECT N'A4',N'Zd4',N'A1/2'
UNION ALL SELECT N'A5',N'Zd5',N'A1/2'
UNION ALL SELECT N'A6',N'Zd6',NULL
UNION ALL SELECT N'A7',N'Zd7',NULL
UNION ALL SELECT N'A8',N'Zd8',N'A4+A5+A6+A7'
/************/
/*Test Data*/
/***fcuandy**/
/*2008-11-26*/
/************/
;
WITH fc(xm,bds,lvl)
AS
(
SELECT xm,CAST(bds AS NVARCHAR(MAX)),1
FROM @t
WHERE bds IS NOT NULL
UNION ALL
SELECT b.xm,REPLACE(b.bds,a.xm,a.zd),b.lvl+1
FROM @t a,fc b
WHERE CHARINDEX(a.xm,b.bds)>0
)
SELECT DISTINCT a.xm,b.bds
FROM @t a
LEFT JOIN
(SELECT x.* FROM fc x
INNER JOIN
(
SELECT MAX(lvl) ml,xm FROM fc GROUP BY xm
) y
ON x.xm=y.xm AND x.lvl=y.ml
) b
ON a.xm=b.xm
/*
A1 NULL
A2 NULL
A3 NULL
A4 Zd1/2
A5 Zd1/2
A6 NULL
A7 NULL
A8 Zd4+Zd5+Zd6+Zd7
*/
create table tb(xm varchar(50),zd varchar(50),bds varchar(50))
insert into tb select 'A1','Zd1',''
insert into tb select 'A2','Zd2',''
insert into tb select 'A3','Zd3',''
insert into tb select 'A4','Zd4','A1/2'
insert into tb select 'A5','Zd5','A1/2'
insert into tb select 'A6','Zd6',''
insert into tb select 'A7','Zd7',''
insert into tb select 'A8','Zd8','A4+A5+A6+A7'
--1.用动态语句生成
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from
(
select m.xm , m.zd , bds = replace(m.bds , n.xm , n.zd) from tb m , tb n where charindex(n.xm , m.bds) > 0 and charindex('/' , m.bds) > 0
union all
select * from tb where charindex('/' , bds) = 0
) t
print @output
/*
Zd4=(Zd1/2),Zd5=(Zd1/2),Zd1,Zd2,Zd3,Zd6,Zd7,Zd8=(A4+A5+A6+A7)
*/
--2.存储过程
go
create procedure my_proc @@str varchar(100) OUTPUT
as
select @@str = coalesce(@@str + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from
(
select m.xm , m.zd , bds = replace(m.bds , n.xm , n.zd) from tb m , tb n where charindex(n.xm , m.bds) > 0 and charindex('/' , m.bds) > 0
union all
select * from tb where charindex('/' , bds) = 0
) t
go
declare @@str as varchar(100)
exec my_proc @@str output
select @@str
/*
Zd4=(Zd1/2),Zd5=(Zd1/2),Zd1,Zd2,Zd3,Zd6,Zd7,Zd8=(A4+A5+A6+A7)
(所影响的行数为 1 行)
*/
drop table tb
drop procedure my_proc
if object_id('tb') is not null
drop table tb
go
create table tb(xm varchar(50),zd varchar(50),bds varchar(50))
insert into tb select 'A1','Zd1',''
insert into tb select 'A2','Zd2',''
insert into tb select 'A3','Zd3',''
insert into tb select 'A4','Zd4','A1/2'
insert into tb select 'A5','Zd5','A1/2'
insert into tb select 'A6','Zd6',''
insert into tb select 'A7','Zd7',''
insert into tb select 'A8','Zd8','A4+A5+A6+A7'
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'=('+bds+')' end from tb
select @sql
create table tb(xm varchar(50),zd varchar(50),bds varchar(50))
insert into tb select 'A1','Zd1',''
insert into tb select 'A2','Zd2',''
insert into tb select 'A3','Zd3',''
insert into tb select 'A4','Zd4','A1/2'
insert into tb select 'A5','Zd5','A1/2'
insert into tb select 'A6','Zd6',''
insert into tb select 'A7','Zd7',''
insert into tb select 'A8','Zd8','A4+A5+A6+A7'
--1.用动态语句生成
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb
print @output
/*
Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)
*/
--2.存储过程
go
create procedure my_proc @@str varchar(100) OUTPUT
as
select @@str = coalesce(@@str + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb
go
declare @@str as varchar(100)
exec my_proc @@str output
select @@str
/*
Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)
(所影响的行数为 1 行)
*/
drop table tb
drop procedure my_proc
set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(xm varchar(10), zd varchar(10), bds varchar(20))
------------------------------
insert tb select 'A1' ,'Zd1',''
insert tb select 'A2' ,'Zd2', ''
insert tb select 'A3', 'Zd3', ''
insert tb select 'A4' ,'Zd4', 'A1/2'
insert tb select 'A5' ,'Zd5', 'A1/2'
insert tb select 'A6' ,'Zd6', ''
insert tb select 'A7' ,'Zd7', ''
insert tb select 'A8', 'Zd8', 'A4+A5+A6+A7'
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'=('+bds+')' end from tb
select @sql=replace(@sql,xm,zd) from tb
select @sql
/*
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)*/
CREATE TABLE TB(xm VARCHAR(2), zd VARCHAR(3), bds VARCHAR(20))
INSERT TB
SELECT 'A1', 'Zd1' , ''UNION ALL
SELECT 'A2', 'Zd2' , '' UNION ALL
SELECT 'A3', 'Zd3' , '' UNION ALL
SELECT 'A4', 'Zd4', 'A1/2' UNION ALL
SELECT 'A5', 'Zd5', 'A1/2' UNION ALL
SELECT 'A6', 'Zd6' , '' UNION ALL
SELECT 'A7', 'Zd7' , '' UNION ALL
SELECT 'A8', 'Zd8', 'A4+A5+A6+A7'
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+CASE WHEN bds<>'' THEN ZD+'=('+REPLACE(bds,'A','Zd')+')' ELSE zd END
FROM TB
SET @SQL=STUFF(@SQL,1,1,'')
PRINT @SQL
DROP TABLE TB
/*
Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)
*/
create table tb(xm varchar(50),zd varchar(50),bds varchar(50))
insert into tb select 'A1','Zd1',''
insert into tb select 'A2','Zd2',''
insert into tb select 'A3','Zd3',''
insert into tb select 'A4','Zd4','A1/2'
insert into tb select 'A5','Zd5','A1/2'
insert into tb select 'A6','Zd6',''
insert into tb select 'A7','Zd7',''
insert into tb select 'A8','Zd8','A4+A5+A6+A7'
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb
print @output
drop table tb
/*
Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)
*/
create proc 你的存储过程名称
--@参数 varchar(50) 可以加参数
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'='+bds end from tb-- where 列=@参数 这里可以加条件
exec('select '+ @sql +' from 你的表')--你的表 后面可以加条件
end
/*如何将一列中所有的值一行显示
数据源
a
b
c
d
e
结果
a,b,c,d,e
*/
create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go
--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result
----------
a,b,c,d,e,
*/
--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/
drop table tb
create table tb(xm varchar(50),zd varchar(50),bds varchar(50))
insert into tb select 'A1','Zd1',''
insert into tb select 'A2','Zd2',''
insert into tb select 'A3','Zd3',''
insert into tb select 'A4','Zd4','A1/2'
insert into tb select 'A5','Zd5','A1/2'
insert into tb select 'A6','Zd6',''
insert into tb select 'A7','Zd7',''
insert into tb select 'A8','Zd8','A4+A5+A6+A7'
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'='+bds end from tb
exec('select '+ @sql +' from 你的表')