我现在有个表 字段如下
standards amount variation statuss Reason
1. 55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;
2. 55 279 4 物量积压 部件人员不足;
3. 55 279 4 物量积压 跨间场地积压;图纸问题较多;
4.
5.
不确定多少行,
我想得到如下查询前面几个字段一样最后一列合并成一列,上面那个表也是我通过一段SQL 查出来的不是现成的表,查询语句 如下
select Z.标准 as standards,
Z.实际数量 as amount,
Z.差异 as variation,
Z.statuss as statuss,
Y.Reason as Reason
from
(
select 55 As 标准,
SUM(B.SessionNum) As 实际数量,
(SUM(B.SessionNum)-55)/55 As 差异,
case
when (SUM(B.SessionNum)-55)/55>=-1 AND (SUM(B.SessionNum)-55)/55<0.05 then '物量不足'
when (SUM(B.SessionNum)-55)/55>=0.05 AND (SUM(B.SessionNum)-55)/55<=0.15 then '正常'
when (SUM(B.SessionNum)-55)/55>0.15 AND (SUM(B.SessionNum)-55)/55<10 then '物量积压'
end As statuss
from
(
select ShipID,SessionID from ShipProgress_Pretreatment where
(EndDate_Delivery is null or EndDate_Delivery='' or convert(datetime,EndDate_Delivery)<='2010-11-24' )
AND
(EndDate_C is null or EndDate_C='' or convert(datetime,EndDate_C)>'2010-11-24' )
) A
inner join T_ShipSession B on A.ShipID=B.ShipID AND B.SessionID=A.SessionID
) Z
Left join T_BufferReason Y on Z.statuss=Y.BufferStatus AND Y.BufferType='1'
最终效果如下
standards amount variation statuss Reason
55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;部件人员不足;跨间场地积压;图纸问题较多;
standards amount variation statuss Reason
1. 55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;
2. 55 279 4 物量积压 部件人员不足;
3. 55 279 4 物量积压 跨间场地积压;图纸问题较多;
4.
5.
不确定多少行,
我想得到如下查询前面几个字段一样最后一列合并成一列,上面那个表也是我通过一段SQL 查出来的不是现成的表,查询语句 如下
select Z.标准 as standards,
Z.实际数量 as amount,
Z.差异 as variation,
Z.statuss as statuss,
Y.Reason as Reason
from
(
select 55 As 标准,
SUM(B.SessionNum) As 实际数量,
(SUM(B.SessionNum)-55)/55 As 差异,
case
when (SUM(B.SessionNum)-55)/55>=-1 AND (SUM(B.SessionNum)-55)/55<0.05 then '物量不足'
when (SUM(B.SessionNum)-55)/55>=0.05 AND (SUM(B.SessionNum)-55)/55<=0.15 then '正常'
when (SUM(B.SessionNum)-55)/55>0.15 AND (SUM(B.SessionNum)-55)/55<10 then '物量积压'
end As statuss
from
(
select ShipID,SessionID from ShipProgress_Pretreatment where
(EndDate_Delivery is null or EndDate_Delivery='' or convert(datetime,EndDate_Delivery)<='2010-11-24' )
AND
(EndDate_C is null or EndDate_C='' or convert(datetime,EndDate_C)>'2010-11-24' )
) A
inner join T_ShipSession B on A.ShipID=B.ShipID AND B.SessionID=A.SessionID
) Z
Left join T_BufferReason Y on Z.statuss=Y.BufferStatus AND Y.BufferType='1'
最终效果如下
standards amount variation statuss Reason
55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;部件人员不足;跨间场地积压;图纸问题较多;
CREATE FUNCTION dbo.mstr(
@standards VARCHAR(50),
@amount VARCHAR(50),
@variation VARCHAR(50),
@statuss VARCHAR(50))
RETURNS VARCHAR(200)
AS
BEGIN
declare @result varchar(20)
set @result = ''
select @result = @result + Reason from test
where standards = @standards and
amount = @amount and
variation = @variation and
statuss = @statuss
RETURN @result
ENDselect standards,amount,variation,statuss,dbo.mstr(standards,amount,variation,statuss) from test
--上面字符串写短了 ,改成200
CREATE FUNCTION dbo.mstr(
@standards VARCHAR(50),
@amount VARCHAR(50),
@variation VARCHAR(50),
@statuss VARCHAR(50))
RETURNS VARCHAR(200)
AS
BEGIN
declare @result varchar(200)
set @result = ''
select @result = @result + Reason from test
where standards = @standards and
amount = @amount and
variation = @variation and
statuss = @statuss
RETURN @result
ENDselect standards,amount,variation,statuss,dbo.mstr(standards,amount,variation,statuss) from test
) AS Reasons
FROM yourtable
GROUP BY standards, amount, variation, statuss;
(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),Reason varchar(50),)
insert into #tb values('1','55','279','4','物量积压 加工人员设备不足;T排制作进度较慢;')
insert into #tb values('2','55','279','4','物量积压 部件人员不足;')
insert into #tb values('3','55','279','4','物量积压 跨间场地积压;图纸问题较多;')select * from #tbdeclare @sql varchar(8000)
set @sql = 'select standards,amount,variation,statuss '
select @sql = @sql + ' , max(case Reason when ''' + Reason + ''' then Reason else '''' end) [' + Reason + ']'
from (select distinct Reason from #tb) as a
set @sql = @sql + ' from #tb group by standards,amount,variation,statuss'
print @sql
exec(@sql) 写了个行转列,暂时不能满足条件,请继续改进
select DISTINCT(standards,amount,variation,statuss),dbo.mstr(standards,amount,variation,statuss) from test是不是少了个DISTINCT
GROUP BY standards, amount, variation, statuss
这边还有50分写完 谁帮忙写个,一起结贴.
drop table tb
CREATE TABLE tb
(standards int, amount int, variation int,statuss int,Reason varchar(50),)
insert into tb values(1,55,279,4,'物量积压 加工人员设备不足;T排制作进度较慢;')
insert into tb values(2,55,279,4,' 部件人员不足;')
insert into tb values(3,55,279,4,' 跨间场地积压;图纸问题较多;')select * from tb
drop function dbo.fn_strSumCREATE FUNCTION dbo.fn_strSum(@amount int,@variation int,@statuss int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + Reason FROM tb WHERE amount=@amount
and variation=@variation and statuss=@statuss
RETURN STUFF(@values, 1, 1, '')
END
GO-- 调用函数
SELECT amount,variation,statuss, Reason = dbo.fn_strSum(amount,variation,statuss) FROM tb GROUP BY amount,variation,statuss
CREATE TABLE tb
(standards int, amount int, variation int,statuss int,Reason varchar(50),)
insert into tb values(1,55,279,4,'物量积压 加工人员设备不足;T排制作进度较慢;')
insert into tb values(2,55,279,4,' 部件人员不足;')
insert into tb values(3,55,279,4,' 跨间场地积压;图纸问题较多;')select * from tb
--drop function dbo.fn_strSumCREATE FUNCTION dbo.fn_strSum(@amount int,@variation int,@statuss int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + Reason FROM tb WHERE amount=@amount
and variation=@variation and statuss=@statuss
RETURN STUFF(@values, 1, 1, '')
END
GO-- 调用函数
SELECT amount,variation,statuss, Reason = dbo.fn_strSum(amount,variation,statuss) FROM tb GROUP BY amount,variation,statuss
CREATE TABLE tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),Reason varchar(50))insert into tb values('55','279','4','物量积压','加工人员设备不足;T排制作进度较慢;')
insert into tb values('55','279','4','物量积压','部件人员不足;')
insert into tb values('55','279','4','物量积压','跨间场地积压;图纸问题较多;')
insert into tb values('56','300','4','物量积压','AAAA;')
insert into tb values('56','300','4','物量积压','BBBB;')
insert into tb values('56','300','4','物量积压','CCCC;')
goCreate function [dbo].[F_getReason] (@standards varchar(100),@amount varchar(100),@variation varchar(100),@statuss varchar(100))
returns varchar(8000)
as begin
declare @re varchar(300)
set @re =''select @re = @re +','+Cast(Reason as varchar)
from (
select standards,amount ,variation ,statuss,Reason from tb ---此处用你查询出来的那个数据表
)a
where a.standards=@standards and a.amount=@amount and a.variation=@variation and a.statuss=@statussreturn (stuff(@re,1,1,''))end goselect distinct standards,amount,variation,statuss,Reason=dbo.F_getReason(standards,amount,variation,statuss) from tb drop table tb
drop function [dbo].[F_getReason]
56 300 4 物量积压 AAAA;,BBBB;,CCCC;
create table #t
(
ID int not null primary key identity(1,1),
name varchar(100) not null,
products varchar(100) not null
)
insert into #t
values
('ping','car'),
('ping','bike'),
('ling','water')SELECT B.name,LEFT(UserList,LEN(UserList)-1) as products FROM (
SELECT name,
(SELECT products+',' FROM #t WHERE name=A.name ORDER BY name FOR XML PATH('')) AS UserList
FROM #t A
GROUP BY name
) Bdrop table #t一个小例子,其实网上关于多行合并为一列的还是有很多的资料的。
the result:
name products
ling water
ping car,bike
--DROP TABLE Test
CREATE TABLE Test(
standards INT,
amount INT,
variation INT,
statuss nvarchar(50),
Reason nvarchar(50)
)
CREATE FUNCTION dbo.gkfu_GetReason(
@standards INT,
@amount INT,
@variation INT,
@statuss nvarchar(50)
)
RETURNS NVARCHAR(1000)
BEGIN
DECLARE @reason nvarchar(1000)
SET @Reason = ''
select @reason = @reason + Reason from test where [standards]=@standards AND amount=@amount AND variation=@variation AND statuss=@statuss RETURN @reason
END
GO
INSERT INTO Test([standards], amount, variation, statuss, Reason)
SELECT 55, 279, 4, '物量积压', '加工人员设备不足;T排制作进度较慢;'
UNION ALL
SELECT 55, 279, 4, '物量积压', '部件人员不足;'
UNION ALL
SELECT 55, 279, 4, '物量积压', '跨间场地积压;图纸问题较多; '
SELECT [standards], amount, variation, statuss, dbo.gkfu_GetReason([standards], amount, variation, statuss) AS Reason
FROM test
group by [standards], amount, variation, statuss