How to make specific columns into rows and change specific values in SQL

Question

I know the title is very vague but I didn't know how to explain this question better, if you have any suggestions please do tell me.

I have this table:

CRAccountID DBAccountID Value

And I want to have a row for each CRAccountID and DBAccountID for example:

CRAccountID DBAccountID Value
100          111         10

I want to get these records:

AccountID  Value
100         10
111        -10

If you haven't noticed yet I also want to make the DBAccountID value in minus.

I tried it by selecting both of them and doing a Union but it takes way too much time when the table gets bigger (the table can contain more than 4 million records)

Here's my code:

select CRAccountID as AccountID, Value from MyTable
UNION
select DBAccountID as AccountID, -Value as Value from MyTable

Note: my code is much much bigger than this with almost 4 joins in each union therefore it becomes so slow.

I'm using Microsoft SQL.

If you want every row then a full table scan is required so not sure if there is a quicker option, but are you just selecting specific transactions? — Chris
– Chris, Commented Mar 20, 2021 at 18:23
By transactions are you referring to records in this table? If so, no. I'm trying to get a report of all the data (using paging or such) — potato molato
– potato molato, Commented Mar 20, 2021 at 18:26
One small thing, if you use UNION ALL instead of UNION it doesn't do the step where it tries to de-dupe the result set. — JBrooks
– JBrooks, Commented Mar 20, 2021 at 18:32
Yeah, sorry, forgot that transaction is ambiguous in this context! I did mean every row, as each represents a debit/credit transaction. Good tip about union all, and I see you got a good answer - I didn't know about apply. — Chris
– Chris, Commented Mar 20, 2021 at 20:33

Gordon Linoff · Accepted Answer · 2021-03-20 18:27:25Z

1

You want to unpivot the data. I would recommend apply:

select v.account, v.value
from t cross apply
     (values (t.CRAccountID, t.value),
             (t.DBAccountID, - t.value)
     ) v(account, value) ;

You can express your complex as a CTE or subquery. Or just include it in the query before the cross apply.

answered Mar 20, 2021 at 18:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

potato molato Over a year ago

Awesome! Thank you so much! That's exactly what I needed.

Collectives™ on Stack Overflow

How to make specific columns into rows and change specific values in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related