SQL – Using "UNION ALL" to Convert Horizontal Data to Vertical Data

Introduction Hi, today I am writing a trick which I found while solving my friends problem in SQL. May be you people already aware with this trick but it’s new for me, so I am sharing it with you. Yesterday my friend asked me a problem in SQL, he wants to convert the horizontal data to vertical data, now what is horizontal and vertical data, answer is as follows:

Horizontal Data AndVertical Data

But my friend not having the data in the table instead he’s having the data in a variable as follows:ABC,ABD,ABE,BAC,BAD,BDE And then he wants to convert these values like in the following format so that he can store the values in the table as a single column:ABC ABD ABE BAC BAD BDE For that he’s planning to use a cursor or a while loop, to read the data and get the first three values except the comma and then get the desired result. So to solve his problem I used the UNION ALL clause and created the following querry. DECLARE @vSQL AS VARCHAR(1000), @vCode AS VARCHAR(1000) SELECT @vCode = ‘ABC,ABD,ABE,BAC,BAD,BDE’ SET @vSQL = ‘SELECT ”’ + REPLACE(@vCode, ‘,’, ”’ UNION ALL SELECT ”’) + ”” EXEC (@vSQL) I think the UNION and UNION ALL operators are the best way to solve this type of problem. His problem is solved but I thought let’s suppose we are having the same situations with tables like the images I displayed above. Then how to do this, to get the solution of the same problem with tables I used the below code. /*First i created a table HzData which is having 5 columns.*/ CREATE TABLE HzData ( Code VARCHAR(5), Code1 VARCHAR(5), Code2 VARCHAR(5), Code3 VARCHAR(5), Code4 VARCHAR(5), Code5 VARCHAR(5), ) /*Then i inserted one row in this table.*/ INSERT INTO HzData(Code, Code1, Code2, Code3, Code4, Code5) VALUES(‘ABC’,'ABD’,'ABE’,'BAC’,'BAD’,'BDE’) SELECT * FROM HzData /*Now the same problem with tables can be solved in two ways. you can deside whichever is best sutited you.*/ /*First way to solve this problem.*/ DECLARE @vSQL AS VARCHAR(1000) SELECT @vSQL = ‘SELECT ”’ + Code + ”’ UNION ALL SELECT ”’ + Code1 + ”’ UNION ALL SELECT ”’ + Code2 + ”’ UNION ALL SELECT ”’ + Code3 + ”’ UNION ALL SELECT ”’ + Code4 + ”’ UNION ALL SELECT ”’ + Code5 + ”” FROM HzData EXEC (@vSQL) /*Second way to solve this problem.*/ DECLARE @vSQL AS VARCHAR(1000), @vCode AS VARCHAR(1000) SELECT @vCode = Code + ‘,’ + Code1 + ‘,’ + Code2 + ‘,’ + Code3 + ‘,’ + Code4 + ‘,’ + Code5 FROM HzData SELECT @vSQL = ‘SELECT ”’ + REPLACE(@vCode, ‘,’, ”’ UNION ALL SELECT ”’) + ”” EXEC (@vSQL) /*You can store this data in the temp table or in a table variable and then use it in your code.*/ I don’t know this is the right or the wrong way to solve this problem, but I found this topic intresting so that I posted this for you people. Please give your comments and suggestion about this post. Thank you, Reference : Prashant Pandey (TechAhead)

*******