Use Order By and Group By with XML Path in SQL Server Query

arjunsin...

SOLVED CLOSED ACTIVE ANSWERED
User: arjunsingh
Posted: on Jun 10, 2015 05:02 AM
Forum: SQL Server
Answer: 1
Views: 20093

My SQL Script

SELECT [TableNumber],[Text] = STUFF((
    SELECT ' ,' + [Text] FROM [GitaDummy].[dbo].[Deletetest] 
    WHERE TableNumber = x.TableNumber
	 FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM [GitaDummy].[dbo].[Deletetest] AS x 
GROUP BY TableNumber

My Table Structure

Main

Result i Get From Above Script

Sir I want the Result to be Orderby A,C,B I have mentioned Orderarranged column. Not able to fix it

Mudassar

0 1

Reply
ANSWER
User: Mudassar
Replied: on Jun 10, 2015 05:03 AM
Report

This way

DECLARE @MyTable TABLE (TableNumber INT, [Text] VARCHAR(1), OrderArranged INT)

INSERT INTO @MyTable
SELECT 1, 'A', 1
UNION ALL
SELECT 1, 'B', 3
UNION ALL
SELECT 1, 'C', 2

SELECT [TableNumber],[Text] = STUFF((
    SELECT ' ,' + [Text] FROM @MyTable
    WHERE TableNumber = x.TableNumber
	ORDER BY OrderArranged ASC
     FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM @MyTable AS x
GROUP BY TableNumber

Use Order By and Group By with XML Path in SQL Server Query

Company

Explore

Follow us

Contact Us