How to convert multiple rows into single row in SQL Server 2008?

/
0 Comments
How to convert multiple rows into single row in SQL Server 2008?

Today we will go through the steps in order to write the simplest query to combine multiple rows into single row in SQL Server 2008: 

Step 1: Create a table

CREATE TABLE [dbo].[Students]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL DEFAULT (NULL),
[gender] [char](1) NULL
)
GO

Step 2: Insert values into the table created in Step 1

INSERT INTO Students (age,gender) VALUES (24,'M')
INSERT INTO Students (age,gender) VALUES (26,'F')
INSERT INTO Students (age,gender) VALUES (25,'F')

Step 3: Below is the query to get multiple rows data in a single row and in a single column

DECLARE @singleRowData NVARCHAR(MAX)=''
SELECT @singleRowData=@singleRowData+CAST(Id AS VARCHAR)+':'+CAST(age AS VARCHAR)+':'+gender+';' FROM [dbo].[Students]

SELECT @singleRowData AS 'singleRowOutput'

Step 4: The output of the query would be as below:


Conclusion:

Today we saw how we can write a query in order to group multiple rows data into a single row & in a single column.



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