SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help to combine row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

96 Posts

Posted - 08/18/2014 :  15:51:54  Show Profile  Reply with Quote
I've table and data as follow,

CREATE TABLE [dbo].[JobNotice_DetailRequirement_2_1](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[JobNoticeID] [int] NULL,
	[Requirement_Expression] [nvarchar](200) NULL
SET IDENTITY_INSERT [dbo].[JobNotice_DetailRequirement_2_1] ON
INSERT [dbo].[JobNotice_DetailRequirement_2_1] ([idx], [JobNoticeID], [Requirement_Expression]) VALUES (1, 78, N'[LevelID]=12 OR')
INSERT [dbo].[JobNotice_DetailRequirement_2_1] ([idx], [JobNoticeID], [Requirement_Expression]) VALUES (2, 78, N'[LevelID]=2 and [StudyFieldID]=72 and [CGPA] >=2.00 and [CGPA]<=4.00')
INSERT [dbo].[JobNotice_DetailRequirement_2_1] ([idx], [JobNoticeID], [Requirement_Expression]) VALUES (3, 109, N'[LevelID]=20 and [StudyFieldID]=24 and [CGPA] >=2.00 and [CGPA]<=4.00 OR')
INSERT [dbo].[JobNotice_DetailRequirement_2_1] ([idx], [JobNoticeID], [Requirement_Expression]) VALUES (4, 109, N'[LevelID]=45')
SET IDENTITY_INSERT [dbo].[JobNotice_DetailRequirement_2_1] OFF

I want my output as follow,

JobNoticeID     | Requirement_Expression
78              | [LevelID]=12 OR [LevelID]=2 and [StudyFieldID]=72 and [CGPA] >=2.00 and [CGPA]<=4.00

109             | [LevelID]=20 and [StudyFieldID]=24 and [CGPA] >=2.00 and [CGPA]<=4.00 OR [LevelID]=45

How my query looks like?

Flowing Fount of Yak Knowledge

2678 Posts

Posted - 08/19/2014 :  08:07:57  Show Profile  Reply with Quote
Here's one way:

SELECT j1.[JobNoticeID]
FROM [JobNotice_DetailRequirement_2_1] j1
    SELECT (
            SELECT [Requirement_Expression] + ' '
            FROM [JobNotice_DetailRequirement_2_1] j2
            WHERE j1.JobNoticeID = j2.JobNoticeID
            FOR XML path('')
            ).value('.', 'nvarchar(max)')
    ) j2([Requirement_Expression])
GROUP BY j1.[JobNoticeID]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000