-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
  • 704,970
  • 168
  • 1,303
  • 1,425
Bibin
  • 195
  • 1
  • 2
  • 9

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
  • 33,725
  • 2
  • 39
  • 60
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
  • 578
  • 3
  • 9