After hours googling pivot and cursors. I couldn't think or figure out how to do this.
I have this query:
declare @banco varchar(100)
set @banco = 'CascVW'
declare @tbltemp1 table
(
prodt_8 float,
prodt_9 float,
prodt_10 float,
prodt_11 float,
prodt_12 float,
prodt_13 float,
prodt_14 float,
prodt_15 float,
prodt_16 float,
prodt_17 float,
prodt_18 float,
prodt_19 float,
prodt_20 float,
prodt_21 float
)
insert into @tbltemp1
Select
Round(Case When Round(Avg(Convert(float,HC08)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod08)),1) / Round(Avg(Convert(float,HC08)),1) End,1) As Prodt_8,
Round(Case When Round(Avg(Convert(float,HC09)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod09)),1) / Round(Avg(Convert(float,HC09)),1) End,1) As Prodt_9,
Round(Case When Round(Avg(Convert(float,HC10)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod10)),1) / Round(Avg(Convert(float,HC10)),1) End,1) As Prodt_10,
Round(Case When Round(Avg(Convert(float,HC11)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod11)),1) / Round(Avg(Convert(float,HC11)),1) End,1) As Prodt_11,
Round(Case When Round(Avg(Convert(float,HC12)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod12)),1) / Round(Avg(Convert(float,HC12)),1) End,1) As Prodt_12,
Round(Case When Round(Avg(Convert(float,HC13)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod13)),1) / Round(Avg(Convert(float,HC13)),1) End,1) As Prodt_13,
Round(Case When Round(Avg(Convert(float,HC14)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod14)),1) / Round(Avg(Convert(float,HC14)),1) End,1) As Prodt_14,
Round(Case When Round(Avg(Convert(float,HC15)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod15)),1) / Round(Avg(Convert(float,HC15)),1) End,1) As Prodt_15,
Round(Case When Round(Avg(Convert(float,HC16)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod16)),1) / Round(Avg(Convert(float,HC16)),1) End,1) As Prodt_16,
Round(Case When Round(Avg(Convert(float,HC17)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod17)),1) / Round(Avg(Convert(float,HC17)),1) End,1) As Prodt_17,
Round(Case When Round(Avg(Convert(float,HC18)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod18)),1) / Round(Avg(Convert(float,HC18)),1) End,1) As Prodt_18,
Round(Case When Round(Avg(Convert(float,HC19)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod19)),1) / Round(Avg(Convert(float,HC19)),1) End,1) As Prodt_19,
Round(Case When Round(Avg(Convert(float,HC20)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod20)),1) / Round(Avg(Convert(float,HC20)),1) End,1) As Prodt_20,
Round(Case When Round(Avg(Convert(float,HC21)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod21)),1) / Round(Avg(Convert(float,HC21)),1) End,1) As Prodt_21
from
tblDadosProd
Where
DescProjeto = @banco
select * from @tbltemp1
the results will be like this:
I need to make a TOP 3 in prodt_8
to prodt_14
and prodt_15
to prodt_21
, but i can't do this with columns. So, I need something like this:
I've created a table and tried to insert, but with no success.
This cursor is not working either:
--DECLARE @AccountID INT
--DECLARE @getAccountID CURSOR
--SET @getAccountID = CURSOR FOR
--SELECT prodt_8,prodt_9
--FROM @temp1
--OPEN @getAccountID
--FETCH NEXT
--FROM @getAccountID INTO @AccountID
--WHILE @@FETCH_STATUS = 0
--BEGIN
--PRINT @AccountID
--FETCH NEXT
-- FROM @getAccountID INTO @AccountID
--END
--CLOSE @getAccountID
--DEALLOCATE @getAccountID
And this Pivot is even worse:
select u.p814, up1521
from #tbltemp1 as u
pivot
( prodt_8,prodt_9)
How can i make this?
Thank you very much.
There are lots of questions here, but it helps only the asker.