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:

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.

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

• Michael Green: 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.