Merge two columns SQL Server

Question

T1

T2 result from unpivot T1

Unpivot query:

SELECT LocM, convert(varchar,[Date],112) as DateKey, FinanceCode, Amount
FROM SalesDetail
UNPIVOT
   ([Amount] FOR [FinanceCode] IN([Sales],[Wages])) AS P

T3

How to join/merge T2 with T3 so the result will be like:

StoreCode | FinanceCode | DateKey  | Amount
070       | Wages       | 20160131 | 3533
070       | Sales       | 20160131 | 833
070       | 0000        | 20160131 | 0

Marcin Zukowski · Accepted Answer · 2016-08-19 05:07:33Z

1

I assume you want such union of records for all dates, not only for 20160131.

Why don't you simply do UNION ALL on T2 and T3, using proper column ordering?

E.g.

SELECT StoreCode, FinanceCode, DateKey, Amount from T3
UNION ALL
SELECT LocM, FinanceCode, DateKey, Amount from T2

You can wrap it in a subquery and add ORDER BY if you want rows from some category to be next to each other.

answered Aug 19, 2016 at 5:07

Marcin Zukowski

4,8191 gold badge25 silver badges31 bronze badges

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

2 Comments

Suhandy Chow Over a year ago

what if the amount of each table columns is not the same? for example what if t2 have 4 columns and t3 have 5 columns

Marcin Zukowski Over a year ago

C'mon, that's a basic SQL thing. If you want to UNION ALL things with different # columns, you add constants with the value you want to see in missing columns. See e.g. here: stackoverflow.com/questions/2309943/…

Collectives™ on Stack Overflow

Merge two columns SQL Server

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related