April 9, 2019 at 2:07 pm
I'm working on a data migration project, moving from one ERP system to another. The way a Sales Ledger account works is different between the two systems and I need to split out accounts from the legacy system into multiple in the new system. The legacy system has a marker in multiple columns depending on types. Each one of these markers would need to be a separate row
Name Country Type 1 Type 2 Type 3 Type 4
AC1 US X X
AC2 UK X X
AC3 US X
How would I separate these into separate rows using SQL, what I would like to see is something like the below
Name Country Type
AC1 US Type 1
AC1 US Type 3
AC2 US Type 2
AC2 US Type 4
AC3 US Type 3
I don't want to export in to Excel and manipulate as I would prefer to keep the transformation in SQL for auditability reasons.
April 9, 2019 at 2:14 pm
April 9, 2019 at 3:17 pm
Personally I'd use CROSS APPLY:
SELECT sl.Name, sl.Country, types.type
FROM Sales_Ledger sl
CROSS APPLY ( VALUES([Type 1]), ([Type 2]), ([Type 3]), ([Type 4]) ) AS types(type)
WHERE types.type > ''
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2019 at 3:20 pm
Unpivot worked perfectly, thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply