如何在sql 2005中获得此输出，分组中的问题 [英] How to get this output in sql 2005, Problem in grouping

问题描述

专家

i有一张这样的桌子

Experts
i have a table like this

Sum       month     MachineType
5         jan        spm
10        jan        echo 
3         jan        sss
8         feb        spm
8         feb        echo
10        mar         sss

我希望这样输出

and i want output like this

month    spm   echo  sss
jan      8      10   3
feb      8      8    -
mar      -      -    10

任何人都可以帮我解决这个问题



提前感谢

（keerthi Kumar）

can any one please help me to solve this issue

thanks in advance
(keerthi Kumar)

推荐答案

看起来您需要使用pivot语句来查看所需格式的数据。



您需要类似的东西：

Hi,

It looks like you neeed to use a pivot statement to view the data in the required format.

You would need something like:

SELECT month, 'spm' AS spm, 'echo' AS echo, 'sss' AS sss
FROM 
(SELECT month, MachineType, Sum_Column
FROM YourTable)
PIVOT
(
SUM (Sum_Column)
FOR YourTable IN
( spm,echo,sss)
) AS pivoted_data

点击* HERE * 。



希望它有所帮助。

Click * HERE * for a relevant article.

Hope it helps.

声明@start DATE = @startDate

声明@end DATE = @endDate

声明re @Month varchar（50）

声明@nature int = @ NatureOfDispatch



;有几个月（日期）

AS

（

SELECT @start

UNION ALL

SELECT DATEADD（月，1，日期）

来自几个月

其中DATEADD（月，1，日期）< = @ end

）

insert ## T1（月）选择日期名称（月份，日期）作为'月份'从几个月



/ *在两个日期之间插入月份* /



/ *将行更新到临时表* /



声明@Sum varchar（50）

声明@monthResult varchar（50）

声明@machineTypeResult varchar（50）





声明cursorResult游标

/ *根据过滤标准从表中选择值* /

SELECT SUM（dbo.MaterialDispatch.ApproxCost）AS Sum，

DATENAME（月，dbo.MaterialDispatch.DeliveryDate）AS月，

dbo.MachineTechnicalDetails.MachineType FROM dbo.MaterialDispatch INNER JOIN dbo.MachineTechnicalDetails

ON dbo.MaterialDispatch.MachineNumber = dbo.MachineTechnicalDetails.MachineNumber

WHERE dbo.MaterialDispatch.DeliveryDate> = @start和dbo.MaterialDispatch.DeliveryDate< = @ end

和dbo.MaterialDispatch.NatureOfDispatch = @nature

GROUP BY DATENAME（month，dbo.MaterialDispatch.DeliveryDate）， dbo.MachineTechnicalDetails.MachineType



/ *选择结束* /



打开cursorResult

从cursorResult获取下一个@ Sum，@ monthResult，@ machineTypeResult

而@@ fetch_status = 0

开始



set @sql ='update ## T1 set'

set @sql + = @machineTypeResult +'='+''''+ @Sum +'''where month = '''+ @monthResult +''''

选择@sql

exec（@sql）

从cursorResult获取下一个到@Sum @ monthRe sult，@ machineTypeResult

if（@@ fetch_status！= 0）

break

end

close cursorResult

deallocate cursorResult



SELECT * FROM ## T1

declare @start DATE = @startDate
declare @end DATE = @endDate
declare @Month varchar(50)
declare @nature int=@NatureOfDispatch

;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date)<=@end
)
insert ##T1(month) select Datename(month,date) as 'Months' from months

/*End of Inserting the months between two dates */

/* UPDATING the Rows into the temporary table */

declare @Sum varchar(50)
declare @monthResult varchar(50)
declare @machineTypeResult varchar(50)


declare cursorResult cursor for
/* Selecting the value from table based on foltering criteria */
SELECT SUM(dbo.MaterialDispatch.ApproxCost) AS Sum,
DATENAME(month, dbo.MaterialDispatch.DeliveryDate) AS month,
dbo.MachineTechnicalDetails.MachineType FROM dbo.MaterialDispatch INNER JOIN dbo.MachineTechnicalDetails
ON dbo.MaterialDispatch.MachineNumber = dbo.MachineTechnicalDetails.MachineNumber
WHERE dbo.MaterialDispatch.DeliveryDate>=@start and dbo.MaterialDispatch.DeliveryDate<=@end
and dbo.MaterialDispatch.NatureOfDispatch= @nature
GROUP BY DATENAME(month, dbo.MaterialDispatch.DeliveryDate), dbo.MachineTechnicalDetails.MachineType

/* end of selecting */

open cursorResult
fetch next from cursorResult into @Sum,@monthResult,@machineTypeResult
while @@fetch_status=0
begin

set @sql='update ##T1 set '
set @sql+= @machineTypeResult +'=' + ''''+ @Sum + ''' where month=''' + @monthResult + ''''
select @sql
exec(@sql)
fetch next from cursorResult into @Sum,@monthResult,@machineTypeResult
if(@@fetch_status!=0)
break
end
close cursorResult
deallocate cursorResult

SELECT * FROM ##T1

这篇关于如何在sql 2005中获得此输出，分组中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！