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)
 Need help to combine row

Author  Topic 

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-08-18 : 15:51:54
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 08:07:57
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
   

- Advertisement -