0

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:

Result of the query

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:

what I need

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.

2 Answers 2

1

If I figured out correctly what you're doing, you could also do it using unpivot, something like this with the separate columns in 2 CTEs and join them side by side using number for row_number:

;with D1 as (
select row_number() over (order by (select null)) as RN, p8_14
from (
  select prodt_8, prodt_9, prodt_10
  from test
) p unpivot (
    p8_14  for colname1 in (prodt_8,  prodt_9,  prodt_10)
) as up),

D2 as (
select row_number() over (order by (select null)) as RN, p15_17
from (
  select prodt_15, prodt_16, prodt_17
  from test
) p unpivot (
    p15_17  for colname1 in (prodt_15, prodt_16,  prodt_17)
) as up)

select p8_14, p15_17 from D1 join D2 on D1.RN = D2.RN

That's just couple of the columns, made also SQL Fiddle: http://sqlfiddle.com/#!6/33d41/1

0

This solved my problem.

It doesnt hurt to think a little

    Select Top 3 [Coluna],[Valor]
From(
     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 = 'CascVW'
    ) X
Unpivot
   ([Valor] For [Coluna] in(Prodt_8,Prodt_9,Prodt_10,Prodt_11,Prodt_12,Prodt_13,Prodt_14,Prodt_15,Prodt_16,Prodt_17,Prodt_18,Prodt_19,Prodt_20,Prodt_21)) U
Where Coluna Not in('Prodt_8','Prodt_9','Prodt_10','Prodt_11','Prodt_12','Prodt_13','Prodt_14')
Order By Valor Desc
1
  • Please mark the answer that solved your problem with a tick, even if it is your own, and vote up those answers which were helpful. Apr 11, 2015 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.