Showing posts with label Query to Combine Multiple Rows to Single. Show all posts
Showing posts with label Query to Combine Multiple Rows to Single. Show all posts
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.

Followers

Popular Posts