Transposing columns

Question

Post reply

Transposing columns

goyers-382153

SSC Veteran

Points: 250
More actions
March 7, 2007 at 1:51 am

#116011

Hi would anyone be kind in helping me resolve this issue, I need to create a view by transposing the current table structure of this table:
Current Structure:
                          ID
                          Amount_type (possible data will be Risk, Opportunities, Account Balances)
                         Amount_1
                         Amount_1_desc
                         Amount_2
                         Amount_2_desc
                         Amount_3
                         Amount_3_desc
                          Amount_4
                          Amount_4_desc
                         Amount_5
                         Amount_5_desc
After transpose:
                           ID
                           AMOUNT_TYPE
                           AMOUNT_DESCRIPTION
                           AMOUNT
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

Can u try it,

create

view vwTranspose

as

select

ID

,

Amount_Type

,

Amount_Description = case (Amount_Type)

when 'Risk' then Amount_1_Desc

when 'Opportunities' then Amount_2_Desc

when 'Account' then Amount_3_Desc

......

else 'Unknown'

end

, Amount

from <tableName>

Kishore.P

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Your question is little ambiguous, it would make it more clear if you could just post with some sample data as to how it is currently stored in the table and how you want it to be transposed in your view.

Prasad Bhogadi
www.inforaise.com

goyers-382153 SSC Veteran Points: 250 More actions · Answer 3

Here's an example:

CURRENT

ID AmtType Amt_1 Amt_1_desc Amt_2 Amt_2_desc Amt_3 Amt_3_desc

1 Risk 100 RISK_1 200 Rsk_2 300 desc_3

2 Opportunities 600 Desc_1 890 Opp_2 700 Opprt desc

3 Acct Balance 10 Balance 50 Bal_amt 20 Desc_bal

TRANSPOSED

ID Type Amt_DESC Amt

1 Risk RISK_1 100

1 Risk Rsk_2 200

1 Risk desc_3 300

2 Opportunities Desc_1 600

2 Opportunities Opp_2 890

2 Opportunities Opprt desc 700

etc.

Note that the only constant is the type and description columns may vary

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

CREATE VIEW MyView

AS

SELECT ID,

AmtType AS Type,

Amt_1_desc AS [Amt Desc],

Amt_1 AS Amt

FROM

MyTable

UNION ALL

SELECT ID,

AmtType AS Type,

Amt_2_desc AS [Amt Desc],

Amt_2 AS Amt

FROM

MyTable

UNION ALL

SELECT ID,

AmtType AS Type,

Amt_3_desc AS [Amt Desc],

Amt_3 AS Amt

FROM

MyTable

GO

SELECT * FROM MyView

ORDER BY ID

Prasad Bhogadi
www.inforaise.com

goyers-382153 SSC Veteran Points: 250 More actions · Answer 5

thank you very much for the help.....it's now working