SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

Idyana
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
) ON [PRIMARY]
GO
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?

gbritton
Flowing Fount of Yak Knowledge

1770 Posts

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


SELECT j1.[JobNoticeID]
    ,max(j2.[Requirement_Expression])
FROM [JobNotice_DetailRequirement_2_1] j1
CROSS APPLY (
    SELECT (
            SELECT [Requirement_Expression] + ' '
            FROM [JobNotice_DetailRequirement_2_1] j2
            WHERE j1.JobNoticeID = j2.JobNoticeID
            FOR XML path('')
                ,type
            ).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.19 seconds. Powered By: Snitz Forums 2000