Get result based on group in sql

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

Latest Posts