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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 3:20 pm
Unpivot worked perfectly, thanks
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy