How to convert a vertical list to a horizontal one in SQL

---

Download xSQL Compare Bundle, starting from $0!

---

*Originally published in our old blog on August 12,  2011

Let's say you have a table that looks something like this:

CREATE TABLE [Source_Vertical](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [GroupID] [int] NOT NULL,
 [Col1_Group] [varchar](50) NULL,
 [Col2_Group] [varchar](50) NULL,
 [Col1_Individual] [varchar](50) NULL,
 [Col2_Individual] [varchar](50) NULL)

You don't know how many rows there are for each GroupID and your job is to generate a list that contains all values associated with a given GroupID in a single row. Here is a simple way to do it:

First: create a target table that looks something like this:

CREATE TABLE [Target_Horizontal](
 [GroupID] [int] NOT NULL,
 [GroupValues] [varchar](max) NULL)

Second: copy, adjust based on your needs, and execute the following simple script (note I used a vertical line as a value separator - you can use whatever you need, just make sure the character you choose as a separator does not exist inside the actual values of the columns):

DECLARE @GroupID int
DECLARE @GroupID_Current int
DECLARE @Col1_Group varchar(50)
DECLARE @Col2_Group varchar(50)
DECLARE @Col1_Individual varchar(50)
DECLARE @Col2_Individual varchar(50)
DECLARE list_cursor CURSOR
    FOR SELECT * FROM dbo.Source_Vertical ORDER BY GroupID
OPEN list_cursor
FETCH NEXT FROM list_cursor INTO 
 @GroupID,
 @Col1_Group,
 @Col2_Group,
 @Col1_Individual,
 @Col2_Individual
 SET @GroupID_Current = ''
WHILE @@FETCH_STATUS = 0
 BEGIN
 
  IF @GroupID_Current <> @GroupID
   BEGIN
    INSERT INTO Target_Horizontal (Id, GroupValues) VALUES(@GroupID, @Col1_Group + '|' + @Col2_Group + '|' + @Col1_Individual + '|' + @Col2_Individual)
    SET @GroupID_Current = @GroupID
   END
  ELSE
   BEGIN
    UPDATE Target_Horizontal SET GroupValues = GroupValues + '|' + @Col1_Individual + '|' + @Col2_Individual WHERE GroupID = @GroupID
   END
 
  FETCH NEXT FROM list_cursor INTO 
   @GroupID,
   @Col1_Group,
   @Col2_Group,
   @Col1_Individual,
   @Col2_Individual
 END
CLOSE list_cursor;
DEALLOCATE list_cursor;

Finally, you can take the results from the target table and dump them onto Excel or wherever you need to.