Click here to Skip to main content
15,900,653 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi, 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


can any one please help me to solve this issue

thanks in advance
(keerthi Kumar)
Posted

Hi,

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

You would need something like:
SQL
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


Click * HERE * for a relevant article.

Hope it helps.
 
Share this answer
 
Comments
Keerthi Kumar(Andar) 3-Sep-13 0:42am    
Syntax error near Piot
here machine type is not fixed it may change based on the requirement
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900