Câu
31
32
33
34
35
36
37
38
39
40
kết quả là:
ID | Câu
1 | 31,32,33,34,35
2 | 36,37,38,39,40
Code như sau:
USE [TN_DB]
GO
/****** Object: StoredProcedure [dbo].[sp_CauCoDinh] Script Date: 11/17/2015 07:26:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CauCoDinh]
AS
BEGIN
DECLARE @i int
DECLARE @j int
CREATE TABLE [dbo].[#BangCauCD]
(
[ID] [int] ,
[Cau] [Nvarchar](500)NOT NULL,
)
DECLARE @cauCD bigint
DECLARE @mon nvarchar(50)
SET @i= 0
SET @j= 1
DECLARE CauCDlst CURSOR FOR
SELECT Stt_Mon
FROM MultiChoise
WHERE (MaMon = 'AnhGE_A4 ') AND (Mucdo = '4')
OPEN CauCDlst
/**/
FETCH NEXT FROM CauCDlst INTO @mon
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cauCD = (SELECT Stt_Mon FROM MultiChoise WHERE (MaMon = 'AnhGE_A4 ') AND (Mucdo = '4') AND (Stt_Mon = @mon))
BEGIN
INSERT INTO [#BangCauCD] (ID,Cau)
VALUES (@j,@cauCD)
END
FETCH NEXT FROM CauCDlst INTO @mon;
SET @i=@i+1
if (@i % 10 = 0) /* chia ra so cau/ de */
begin
SET @j = @j +1
end
END
CLOSE CauCDlst
DEALLOCATE CauCDlst;
/*
SELECT * FROM [#BangCauCD]
ORDER BY ID ASC
*/
SELECT DISTINCT C2.ID, /*chuyển nhiều hàng giống nhau về một cột*/
SUBSTRING(
(
SELECT ','+C1.Cau
FROM #BangCauCD C1
WHERE C1.ID = C2.ID
ORDER BY C1.ID
FOR XML PATH ('')
), 2, 1000) DeThiCoDinh
FROM #BangCauCD C2
END
/* exec [dbo].[sp_CauCoDinh]
*/
chúc các bạn thành công
