Get result based on group in sql

Posted by Jcpvivek under Sql Server on 12/4/2013 | Points: 10 | Views : 700 | Status : [Member] | Replies : 3
Need your help

My problem :

QARowID RowID QuestionID QuestionTag QuestionTitle QuestionAnswer
1 4 0000-000000000000 DEFAULT_001 Q1
2 4 0000-0000-000000000000 DEFAULT_002 Q2
3 4 0000-0000-000000000000 DEFAULT_003 Q3
4 7 0000-0000-000000000000 DEFAULT_001 Q1 asd
5 7 0000-0000-000000000000 DEFAULT_002 Q2 cx xc
6 7 0000-0000-000000000000 DEFAULT_003 Q3 wer
7 9 0000-0000-000000000000 DEFAULT_001 Q1 c xc
8 9 0000-0000-000000000000 DEFAULT_002 Q2 hh
9 9 0000-0000-000000000000 DEFAULT_003 Q3 dd
10 10 0000-0000-000000000000 DEFAULT_001 Q1 rr
Result output

Questionandanswer
Q1:asd,cxc,rr
Q2:cxxc,hh
Q3:wer,dd


I am working on sql server 2000 ,i used cursor for this but giving me performance issue.
I cannot user for xml path in 2000 not even CTE also.

Please suggest with best option what can i do to get the output




Responses

Posted by: Bandi on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
GO
CREATE function DBO.UFN_Form_Comma_Sep_Values(
@p_QuesTitle VARCHAR(5))
Returns VARCHAR(4000)
AS
BEGIN
declare @csv varchar(1000);
select @csv = ISNULL(@csv, '') + CAST(QuestionAnswer as varchar(10))+','
from tab
WHERE QuestionTitle = @p_QuesTitle
return substring(@csv,1, LEN(@csv)-1);
END;

SELECT DISTINCT QuestionTitle , dbo.UFN_Form_Comma_Sep_Values(QuestionTitle) FROM tab


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jcpvivek, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer
http://www.sqlmusings.com/2009/03/15/how-to-format-query-result-as-comma-separated-values-csv/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jcpvivek, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You can use while loop instead of cursor in stored procedure as below example:-

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Jcpvivek, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response