Request To Help me in a SQL Server() Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Request To Help me in a SQL Server() Query

Author

Topic

fasihakbar590
Starting Member

4 Posts

Posted - 2010-12-10 : 08:36:49

Dear Friend(s),

I need to write a query based on following scenario:-

My Table Structure and data in the table is as follows:-

ID Desc ParentID
1 Test 1 Null
2 Test 2 1
3 Test 3 1
4 Test 4 Null
5 Test 5 4
6 Test 6 4
7 Test 7 4

I have written the following query to get Data

SELECT t2.Desc as 'Parent Desc',t1.Desc as 'Child'
FROM tbltest t1
INNER JOIN tbltest t2 on t2.ID = t1.ParentID

The result of the above query will be like this :-
Parent Desc Child
Test 1 Test 2
Test 1 Test 3
Test 4 Test 5
Test 4 Test 6
Test 4 Test 7

But I need to write query which will give This result :-
Parent Desc Child
Test 1 Test 2,Test 3
Test 4 Test 5,Test 6,Test 7

Please help me. I shall be very thakful to you

Regards,
M.Fasih Akbar

fasihakbar590

PavanKK
Starting Member

32 Posts

Posted - 2010-12-10 : 10:02:54

This will help...

DECLARE @Table TABLE (ID INT,[Desc] VARCHAR(30),ParentID INT)

INSERT INTO @Table
VALUES( 1,'Test 1', Null),(2 ,'Test 2', 1),(3 ,'Test 3', 1),(4 ,'Test 4', Null),(5 ,'Test 5', 4),(6 ,'Test 6', 4),(7 ,'Test 7', 4)

SELECT A.ParentDesc,A.ChildDesc FROM (
SELECT T.[Desc] AS ParentDesc,( SELECT STUFF((SELECT ','+I.[Desc] FROM @Table I WHERE I.ParentID = T.ID FOR XML PATH('') ),1,1,'') ) AS ChildDesc
FROM @Table T
)A
WHERE A.ChildDesc IS NOT NULL

KK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-10 : 10:30:22

A more generic solution would be this (just in case there are more than two levels in the hierarchy).


DECLARE	@Sample TABLE
	(
		ID INT,
		[Desc] VARCHAR(20),
		ParentID INT
	)

INSERT	@Sample
VALUES	(1, 'Test 1', NULL),
	(2, 'Test 2', 1),
	(3, 'Test 3', 1),
	(4, 'Test 4', NULL),
	(5, 'Test 5', 4),
	(6, 'Test 6', 4),
	(7, 'Test 7', 4)

;WITH cteIteration
AS (
	SELECT	ID AS RootID,
		[Desc] AS RootDesc,
		ID,
		[Desc]
	FROM	@Sample
	WHERE	ParentID IS NULL

	UNION ALL

	SELECT		i.RootID,
			i.RootDesc,
			s.ID,
			s.[Desc]
	FROM		@Sample AS s
	INNER JOIN	cteIteration AS i ON i.ID = s.ParentID
)
SELECT		r.RootDesc,
		STUFF(f.Data.value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Children
FROM		(
			SELECT	DISTINCT
				RootID,
				RootDesc
			FROM	cteIteration
		) AS r
CROSS APPLY	(
			SELECT DISTINCT	', ' + i.[Desc]
			FROM		cteIteration AS i
			WHERE		i.RootID = r.RootID
			ORDER BY	', ' + i.[Desc]
			FOR XML		PATH(''),
					TYPE
		) AS f(Data)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources