![]() |
query problem
Hi everyone,
i am having a problem with this query, plz anyone help me regarding this problem, i will be very thankful Suppose we hav a table with two fields - id and state id is not unique means one to many relation in id and state for e.g 1 Delhi 1 Haryana 1 Punjab 2 Maharashtra 2 Goa .... now we want our result in following form 1 delhi,Haryana,Punjab 2 Maharashtra,Goa how we can achhieve it in single query without using cursor??? thanks for reading and your consideration all suggestions are welcome, thanks & regards ashok sharma |
First, you have to create a user-defined function that will accept the ID as the parameter and return the comma-delimited states. Here's a link that may be able to help you:
http://www.sql-server-helper.com/tip...ed-output.aspx SQL Server Helper How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!! http://www.sql-server-helper.com/free-test/default.aspx Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp |
Hi,
I have prepared the following sql commands in order to solve this problem. First of all I created a table to include sample data. Later I prepared a udf that I'll use in the final sql statement. Here is the sql commands to simulate the problem. create table t ( id int, description varchar(15) ) go insert into t select 1, 'Delhi' insert into t select 1, 'Haryana' insert into t select 1, 'Punjab' insert into t select 2, 'Maharashtra' insert into t select 2, 'Goa' go And the User defined function is as follows; CREATE FUNCTION ListOf ( @Id int ) RETURNS nvarchar(255) AS BEGIN DECLARE @str AS nvarchar(max) SELECT @str = COALESCE(@str + ', ', '') + description FROM t (NoLock) WHERE Id = @Id RETURN @str END GO And the final script that I can suggest is as follows: SELECT t.id, dbo.ListOf(t.id) FROM ( SELECT distinct id FROM t ) t I hope this will help you You can find more information on COALESCE usage for similar applications at http://www.kodyaz.com/forums/thread/76.aspx and at http://www.kodyaz.com/articles/artic...x?articleid=29 Eralper Eralper http://www.kodyaz.com |
Hi,
thanks for your reply, but there is a little problem if i use 5000 instead of max, its working, otherwise it is giving me error, plz check Msg 170, Level 15, State 1, Procedure ListOf, Line 10 Line 10: Incorrect syntax near 'max'. Msg 137, Level 15, State 2, Procedure ListOf, Line 13 Must declare the variable '@str'. Msg 137, Level 15, State 2, Procedure ListOf, Line 17 Must declare the variable '@str'. i could not found why is this error thanks for your consideration regards ashok sharma |
Code:
DECLARE @str AS nvarchar(max) _________________________ - Vijay G Strive for Perfection |
All times are GMT -4. The time now is 01:14 AM. |
Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.