December 17, 2007 at 8:00 pm
i am having data something like this:
number starting point ending point
1 ABC DEF
2 DEF ABC
1 PQR STU
2 STU PQR
1 ABC DEF
2 DEF PQR
3 PQR ABC
and i want it to be like this in my new table based on number column:
CONCATENATED
ABC/DEF/ABC
PQR/STU/PQR
ABC/DEF/PQR/ABC
can anyone suggest me how to write a query for this one?
December 18, 2007 at 9:58 am
Sorry... not seeing the pattern here between the inputs and the desired output... would you explain how you want the concatenation formed better, please? Also, see the following if you want better help quicker...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2007 at 10:55 am
Hi Jeff...
thanx for ur help....here is the proper way....
CREATE TABLE customers (
Number [smallint] NULL ,
starting_point [varchar] (5) NULL ,
ending_point [varchar] (5) NULL
)
insert into customers values (1, 'abc', 'def')
insert into customers values (2, 'def', 'abc')
insert into customers values (1, 'pqr', 'stu')
insert into customers values (2, 'stu', 'pqr')
insert into customers values (1, 'abc', 'def')
insert into customers values (2, 'def', 'pqr')
insert into customers values (3, 'pqr', 'abc')
select * from customers
so now i want my output in this manner based on number-
new_column
abc/def/abc
pqr/stu/pqr
abc/def/pqr/abc
i hope i have explained u clearly.....
December 18, 2007 at 10:56 am
Jeff - it's a sequential file setup. look at the multiple segments (1 relates to the next "2" by previous.col3=current.col2).
You're going to need something more than physical order to help you build this. The fact that the file you started from had record sequences of 1,2,3,etc...1,2,3,etc... in order does NOT mean that SQL either inserted them in that physical order OR that the query will return then in that order. That's probably your biggest challenge.
Other than that:
- is there a set number of these sequence per "group" of does it vary.
- Alternatively - do you know the highest sequence in a given group?
- are the occasions to have gaps in the sequence? do they always start @ 1?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 18, 2007 at 11:14 am
Jeff - it's a sequential file setup. look at the multiple segments (1 relates to the next "2" by previous.col3=current.col2).
Heh... I'm getting old... I was looking at trying to relate all of the 1's to 1's, 2's to 2's, etc...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2007 at 11:14 am
Can you add a primary key of some sort that keeps them in the order you want, such as an identity column? That would make it easier to then ensure they are selected in the right order.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 18, 2007 at 11:31 am
You need to add a "GroupID" column, so that the first set is group 1, the second set is group 2, etc.
If you do that, then it becomes much easier to manage the data and build the concatenation.
You'll still need to roll through row by row, but you can at least tell the query where to start and stop each string.
(Alternately, if you know that all sets are <= X rows, you can build a series of left outer joins where the groupid is part of the join, and then use a single coalesce statement to build the string. But that only works if you have a hard-maximum on the number of rows per group.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2007 at 5:16 pm
grkanth81 (12/17/2007)
i am having data something like this:number starting point ending point
1 ABC DEF
2 DEF ABC
1 PQR STU
2 STU PQR
1 ABC DEF
2 DEF PQR
3 PQR ABC
and i want it to be like this in my new table based on number column:
CONCATENATED
ABC/DEF/ABC
PQR/STU/PQR
ABC/DEF/PQR/ABC
can anyone suggest me how to write a query for this one?
What's the difference between 2 lines
1 ABC DEF
?
Why one of them must be followed by
2 DEF ABC
but another one must be followed by
2 DEF PQR
?
How to decide which line to be following by which line?
_____________
Code for TallyGenerator
December 18, 2007 at 7:49 pm
ok guyz..thanx for ur responses...i am trying to provide u more precise information...here it is---i am adding a new field ID and i got this extra information as well--
When the Number = 1, get the starting_point, then concatenate the ending_point from that record as well as any additional segments for that ID.
CREATE TABLE customers (
ID int NOT NULL,
Number [smallint] NULL ,
starting_point [varchar] (5) NULL ,
ending_point [varchar] (5) NULL
)
insert into customers values (888,1, 'abc', 'def')
insert into customers values (888,2, 'def', 'abc')
insert into customers values (777,1, 'pqr', 'stu')
insert into customers values (777,2, 'stu', 'pqr')
insert into customers values (666,1, 'abc', 'def')
insert into customers values (666,2, 'def', 'pqr')
insert into customers values (666,3, 'pqr', 'abc')
select * from customers
so now i want my output in this manner -
new_column
abc/def/abc
pqr/stu/pqr
abc/def/pqr/abc
December 19, 2007 at 10:02 am
Hey guyz...
can anyone help me with the probs i have mentioned in the post...
thnx...
December 19, 2007 at 11:49 am
grkanth81 (12/19/2007)
Hey guyz...can anyone help me with the probs i have mentioned in the post...
thnx...
Well, at least that's a polite way to "bump" your post... 😉 This isn't "instant help", ya know 😛
First, since you're using SQL Server 2000, you need to create a function that looks like this...
CREATE FUNCTION dbo.fnConcatPoints
(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
--===== Declare the return variable
DECLARE @Result VARCHAR(8000)
--===== Find the points for a given ID and concatenate them in order by point Number
SELECT @Result = ISNULL(@Result+',','')+d.Point
FROM (--==== Derived table "d" separates the points
SELECT --This finds all but the last point
Number,Starting_Point AS Point
FROM Customers c
WHERE c.ID = @ID
UNION ALL -----------------------------------------
SELECT --This finds the last point
t1.Number+1,Ending_Point
FROM Customers t1,
(SELECT ID, MAX(Number) AS MaxNumber FROM Customers WHERE ID = @ID GROUP BY ID ) t2
WHERE t1.ID = t2.ID
AND t1.Number = t2.MaxNumber
AND t1.ID = @ID
)d
ORDER BY d.Number
RETURN @Result
END
... note that the function does NOT do any checking to make sure that startpoints and endpoints have a match... I assume that whatever process you have to create the data already ensures that.
Next, you run a query that looks like this...
SELECT ID,dbo.fnConcatPoints(ID) AS Points
FROM Customers
GROUP BY ID
...and that will give you results that look like this...
ID Points
----------- ---------------
666 abc,def,pqr,abc
777 pqr,stu,pqr
888 abc,def,abc
(3 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2007 at 6:40 am
Thanx Jeff for ur response............
December 20, 2007 at 7:30 am
You bet... let me know how it works out for you. And thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2007 at 9:53 am
hey jeff....actually i was trying for this one sterday whole day...and i came to know tht this can be done using cursors or function......so i created the below code using cursors and it worked properly.....
create table new_customers (ITINARY varchar(30))
Declare
@m_int int, @m_1 varchar(10),@m_2 varchar(10), @m_int1 int, @m_str varchar(30)
declare c_insert cursor for SELECT number,
starting_point,ending_point FROM customers
set @m_int = 0
open c_insert
fetch next from c_insert into
@m_int,@m_1,@m_2
set @m_int1 = @m_int
while @@fetch_status = 0
begin
if @m_int = 1
begin
set @m_str = @m_1 +'/'+ @m_2
set @m_int1 = @m_int
end
else if @m_int > 1
begin
set @m_str = @m_str + '/'+ @m_2
set @m_int1 = @m_int
end
fetch next from c_insert into
@m_int,@m_1,@m_2
if @m_int = 1 or @@fetch_status <> 0
insert into new_customers values (@m_str)
end
close c_insert
deallocate c_insert
select * from new_customers
The query which u have sended is also working perfect.....and thnx for it....so now i am two options;)
Thanks
December 20, 2007 at 10:20 am
Thanks for the feedback, but to be sure... cursors are never an option for me. Maybe the occasional WHILE loop to act as a control for managing "dips" to other databases, but never a cursor.
Anyway, I'm very glad to have been able to help. Keep up the good work.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply