Convert columns to rows in SQL Server 2008 without unpivot

/
0 Comments

Convert columns to rows in SQL Server 2008 without unpivot

Today I will show how to write a query in order to convert the columns to rows in SQL Server 2008 without using the unpivot function:

Step 1: Let's analyse the input first which is as shown below:

Convert columns to rows in SQL Server 2008 without unpivot


Step 2: Now lets write a query that would help us inorder to get the columns into rows as shown below:

DECLARE @rowId INT=1,@noOfRows INT=0,@id varchar(20),@age varchar(20),@gender varchar(20)
--First get the no of rows in the table
SET @noOfRows=(SELECT count(*) FROM students)

--create a table variable to store the output data required
DECLARE @tbl TABLE
(
      col1 VARCHAR(20)
)

--Put a while loop to iterate through all the rows in the table one by one:
WHILE(@rowId<=@noOfRows)
BEGIN
      SELECT @id=CAST(id AS VARCHAR),@age=CAST(age AS VARCHAR),@gender=gender FROM students WHERE id=@rowId

      Insert into @tbl (col1) values (@id)
      Insert into @tbl (col1) values (@age)
      Insert into @tbl (col1) values (@gender)

      --increment the @rowId counter by 1 to get the next row in the next iteration
      SET @rowId=@rowId+1;
END

SELECT * FROM @tbl

Step 3: The output will be as shown below:

Convert columns to rows in SQL Server 2008 without unpivot



About Post Author

Microsoft Certified SQL Server Developer

You may also like

No comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts