Transform 14 columns into 2 (SQL Server 2008) - Pivot/Cursor not helping

Question

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.

Answer 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

Answer 2

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