-1

I have a table like this

CREATE TABLE #CurrencyRate
(
     [Base] nvarchar(10), 
     [Quote] nvarchar(10), 
     [Amount] nvarchar(10)
)

and it has data like this

Base   Quote   Amount
---------------------
R1C1   R1C2    R1C3
R2C1   R2C2    R2C3

Note: R1C1 => Row 1, Column 1

I want output like 

Row      Column    Attribute   Value
-----------------------------------------
1          1       Base        R1C1
1          2       Quote       R1C2
1          3       Amount      R1C3
2          1       Quote       R2C1
2          2       Amount      R2C2
2          3       Base        R2C3

Is it possible to get output like this with some SQL?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bibin
  • 215
  • 2
  • 4
  • 10

2 Answers2

1

Unpivot may be simpler, but you also need to generate your row numbers and column numbers somehow... I did this using a window function but was unsure about the Order of the rows in your table.

But if you're only dealing with 3 columns this should work as well.

WITH cte as (
SELECT row_number() 
  over (partition by base,quote,amount order by base, quote Amount) as RN,
Base, Quote, Amount
FROM #CurrencyRate)

SELECT RN as Row, 1 as Column, 'Base', base as value FROM cte
UNION ALL
SELECT RN, 2, 'Quote', quote FROM cte
UNION ALL
SELECT RN, 3, 'Amount', Amount FROM cte
xQbert
  • 34,733
  • 2
  • 41
  • 62
1
select  
    Row,
    row_number() over (partition by row order by Row) Col,
    Value,
    Attribute
from (
    select Base, Quote, Amount,
    row_number() over (order by Base) Row
    from #CurrencyRate c
) a
unpivot
(
    Value for Attribute in ([Base], [Quote], [Amount])
) unpvt;
msheikh25
  • 576
  • 3
  • 9