Showing posts with label Convert columns to rows in SQL Server 2008 without unpivot. Show all posts
Showing posts with label Convert columns to rows in SQL Server 2008 without unpivot. Show all posts

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

Followers

Popular Posts