Transposing Data

[anoopm7]

Good Afternoon !!

I have a SQL query that pulls data and there is two fields called chargeType and ChargeAmt. The charge could be none or multiple values depending on the order. For example

Data looks like this

Ord#    ChargeType#  ChargeAmt#

1896    A-123             $89.69

1896    B-589             $239.69

2026    K-789             $123.56

I would want to convert it to as so that there won't be a unique row for an ord#

Ord#  Chargetyp1 ChargeAmt1  Chargetyp1  ChargeAmt1  ChargeType3 ChargeAmt3

1896  A-123           $89.69          B-589           $239.69          NULL              0

2026  NULL           0                   NULL           0                      K-789             $123.56 

What would be the best way to do it.

[tcroberts]

Sorry can you clarify your desired output a little? I'm unsure what exactly you're looking for, as you've lost the 2026 Order#, and your second row in the desired output appears to have all 3 charges.

Thanks!

[anoopm7]

My apologies. I corrected the way I would like to see the data.

[tcroberts · Accepted solution]

Thanks,

So just to clarify, you'd like each Charge type number to have its own column? i.e. A transaction using A-123 will never be in the same column as K-789?

Or are you looking to generate additional columns for every transaction, with the total number of columns proportional to the ID which has the most transactions associated with it?

In this second case, You'd want the ChargeType1 and ChargeAmt1 to be K-789 and 123.56 respectively, with ChargeType/Amt2 and ChargeType/Amt3 being Null(), correct?

I'm working on getting an example up, but if you could clarify this last thing it would help,

Thanks

EDIT: Here's a screenshot of what something like the first case would look like, which is what it appears to me that your desired output is. It appears that each ChargeType column would only ever have values with the same ChargeType, so instead I've make these ChargeTypes the headers. That being said, this would fail if there were more than 1 transaction of each type for a given ID. The way I've currently got it set up is that it would sum those, and I doubt that is what you'd be looking for.

Spoiler

[anoopm7]

Yes, I wanted to generate columns for every transaction with the total number of columns proportional to the ID. So for some orders few columns could be empty as they might not have values.

You'd want the ChargeType1 and ChargeAmt1 to be K-789 and 123.56 respectively, with ChargeType/Amt2 and ChargeType/Amt3 being Null(), correct? ---> Yes, since Ord#2026 does not have any values associated with ChargeType1 and ChargeType2.

I am excited !!