Merge Multiple Columns in to One
-
I have data like the "before" picture below. I also have an excel file with the same example data pictured below, in case the image doesn't come through.
I'm trying to merge values from several columns (var1-var3) in to one column (AllVar.) I would like the AllVar column to have the values from each of the var1-var3 columns, and the X1 field value to be repeated so that it matches the X1 field value associated the var1-var3 field values from the before data. I hope that's not too confusing. If anyone knows a slick way to do this with sql I'd very much appreciate the tip.
-
You could do something like this:
SELECT X1,
AllVar
FROM your_table
CROSS APPLY
(
SELECT Var1
UNION ALL
SELECT Var2
UNION ALL
SELECT Var3
)x(AllVar)
WHERE AllVar IS NOT NULL
;
Cheers!
-
I'd make a slight adjustment, since I prefer to check conditions as near to the source as possible, and it might even be slightly more efficient:
SELECT X1,
AllVar
FROM dbo.your_table
CROSS APPLY
(
SELECT Var1
WHERE Var1 IS NOT NULL
UNION ALL
SELECT Var2
WHERE Var2 IS NOT NULL
UNION ALL
SELECT Var3
WHERE Var3 IS NOT NULL
) AllVars(AllVar);
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".
-
ScottPletcher (6/14/2016)
I'd make a slight adjustment, since I prefer to check conditions as near to the source as possible, and it might even be slightly more efficient:SELECT X1,
AllVar
FROM dbo.your_table
CROSS APPLY
(
SELECT Var1
WHERE Var1 IS NOT NULL
UNION ALL
SELECT Var2
WHERE Var2 IS NOT NULL
UNION ALL
SELECT Var3
WHERE Var3 IS NOT NULL
) AllVars(AllVar);
I thought that at first, but ran some quick tests before posting and the version I ended up posting was substantially faster on a million row table (something like 650 ms vs 1100 ms for both CPU and elapsed time, if memory serves).
The plan for the second version on my machine (2012 SP2) is estimated to be only slightly more expensive, but introduces a concatenation operator and the separate filter operators, which apparently have a lot more overhead than either we or the optimizer thought.
Cheers!
-
Jacob Wilkins (6/13/2016)
You could do something like this:SELECT X1,
AllVar
FROM your_table
CROSS APPLY
(
SELECT Var1
UNION ALL
SELECT Var2
UNION ALL
SELECT Var3
)x(AllVar)
WHERE AllVar IS NOT NULL
;
Cheers!
I prefer using the table values constructor, because it's less typing.
SELECT X1,
AllVar
FROM your_table
CROSS APPLY
( VALUES(Var1), (Var2), (Var3) )x(AllVar)
WHERE AllVar IS NOT NULL
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply