Need help to transpose rows to columns

Question

I'm sorry to flat out ask for someone to write my code for me but I've been tearing my hair out for nearly an hour trying to get the pivot operator to work in SQL.

I have the following results set:

SCCY AccountedPremiumCurrent AccountedPremiumPrevious
---- ----------------------- ------------------------
CAD  99111.0000              NULL
EUR  467874.0000             128504.0000
GBP  431618.3847             195065.8751
USD  1072301.1193            1171412.1193

And I need to pivot it around to this:

GBP         USD          CAD        EUR
----------- ------------ ---------- -----------
431618.3847 1072301.1193 99111.0000 467874.0000
195065.8751 1171412.1193 NULL       128504.0000

I have a workaround which uses a union across two ugly "select max(case when..." queries but I'd love to get this working with the pivot operator.

I feel like my brain cannot process the necessary logic to perform this operation, hence I'm asking for someone to help. Once I get this I'll be able to hopefully re-apply this like a pro...

the pivot operator is just syntactic sugar, under the hood is still `max(case`.Maybe you want dynamic pivot? — Mihai, Jun 02 '15 at 16:32
@TomRedfern This is full transpose of the table/data. There are a few other questions that will demo this - [one](http://stackoverflow.com/questions/12696225/sql-real-transpose), [two](http://stackoverflow.com/questions/15297809/sql-transpose-full-table), or [three](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — Taryn, Jun 02 '15 at 16:58
@bluefeet - thanks for the comment - yes I can see that I should be asking about transposing rather than pivoting. Have updated question to reflect. — tom redfern, Jun 02 '15 at 17:38
@Mihai thanks for the comment. It may well be syntactic sugar but it sure is nicer to look at! — tom redfern, Jun 02 '15 at 18:25

score 4 · Accepted Answer · answered Jun 02 '15 at 16:47

You can do it with the following query:

SELECT col, [CAD], [EUR], [GBP], [USD]
FROM (
   SELECT SCCY, col, val
   FROM mytable
   CROSS APPLY (SELECT 'current', AccountedPremiumCurrent UNION ALL
                SELECT 'previous', AccountedPremiumPrevious) x(col, val) ) src
PIVOT (
  MAX(val) FOR SCCY IN ([CAD], [EUR], [GBP], [USD])
) pvt

PIVOT on multiple columns, like AccountedPremiumCurrent, AccountedPremiumPrevious in your case is not possible in SQL Server. Hence, the CROSS APPLY trick is used in order to unpivot those two columns, before PIVOT is applied.

In the output produced by the above query, col is equal to current for values coming from AccountedPremiumCurrent and equal to previous for values coming from AccountedPremiumPrevious.

Demo here

score 3 · Answer 2 · edited Jan 18 '21 at 12:30

If you need a Dynamic pivot(where column values are not known in advance), you can do the following queries.

First of all declare a variable to get the column values dynamically

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + SCCY + ']', '[' + SCCY + ']')
               FROM    (SELECT DISTINCT SCCY FROM #TEMP) PV 
               ORDER BY SCCY

Now use the below query to pivot. I have used CROSS APPLY to bring the two column values to one column.

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT ' + @cols + ' FROM 
             (
                 SELECT SCCY,AccountedPremium,
                 ROW_NUMBER() OVER(PARTITION BY SCCY ORDER BY (SELECT 0)) RNO 
                 FROM #TEMP
                 CROSS APPLY(VALUES (AccountedPremiumCurrent),(AccountedPremiumPrevious))
                 AS COLUMNNAMES(AccountedPremium)
             ) x
             PIVOT 
             (
                 MAX(AccountedPremium)
                 FOR SCCY IN (' + @cols + ')
            ) p
            ' 

EXEC SP_EXECUTESQL @query

Click here to view result

Raj More · Answer 3 · 2015-06-02T16:55:05.230

Use PIVOT twice and UNION ALL it like this

With MyTable as
(
    Select 'CAD' SCCY, 99111.0000 AccountedPremiumCurrent, NULL AccountedPremiumPrevious
    Union Select 'EUR', 467874.0000 , 128504.0000
    Union Select 'GBP', 431618.3847 , 195065.8751
    Union Select 'USD', 1072301.1193 , 1171412.1193
)
Select Sum (CAD) Cad, Sum (EUR) Eur, Sum (GBP) GBP, Sum (USD) USD
From MyTable
Pivot 
(
    Sum (AccountedPremiumCurrent) 
    For Sccy In ([CAD], [EUR], [GBP], [USD])
) as P

UNION ALL

Select Sum (CAD) Cad, Sum (EUR) Eur, Sum (GBP) GBP, Sum (USD) USD
From MyTable
Pivot 
(
    Sum (AccountedPremiumPrevious) 
    For Sccy In ([CAD], [EUR], [GBP], [USD])
) as P

This is the answer I got

Cad          Eur            GBP            USD
------------ -------------- -------------- ---------------
NULL         128504.0000    195065.8751    1171412.1193
99111.0000   467874.0000    431618.3847    1072301.1193

score 2 · Answer 4 · answered Jun 02 '15 at 17:26

Your source table can't contain two fields to pivot on, so union them together with some other arbitrary data to differentiate them (in the case below, the [status] field is either 'Current' or 'Previous'). Once you've done that, apply whatever aggregate needs to be applied in the pivot clause.

select [GBP], [USD], [CAD], [EUR] -- or [status], [GBP], [USD], [CAD], [EUR]
from  (select 'Current' as [status], SCCY, AccountedPremiumCurrent as [value] from @data
       union all
       select 'Previous', SCCY, AccountedPremiumPrevious from @data) as SourceTable
pivot (sum([value]) for SCCY in ([CAD], [EUR], [GBP], [USD])) as PivotTable

Need help to transpose rows to columns

4 Answers4