May 28, 2004 at 5:16 am
Hi all can someone please help me with the following: -
I am doing a select followed by a union select, to get data from two tables (parent and description) into the same result set . This is what I am currently getting :-
Parent1
1 Description1
11 Description2
12 Description3
2 Description4
Parent2
13 Description1
123 Description100
3 Descriptionx
this is what i want: -
Parent1
1 Description1
2 Description2
11 Description3
12 Description 4
Parent2
13 Description1
123 Description 100
3 Descriptionx
any ideas
thanks
CCB
May 28, 2004 at 5:37 am
select col1,col2
from (
select col1,col2 from tablea
union
select col1,col2 from tableb
) a
order by cast(col1 as int)
Far away is close at hand in the images of elsewhere.
Anon.
May 28, 2004 at 6:00 am
Thanks for the response. I am not sure this will work, because Parent1 and Parent2 need to be returned in the result set, and their order must not change regardless of their alphabetical order.
Also, I should of mentioned, that the children need to be padded in the follwoing way
1 desc
2 desc
14 desc
300 desc
any ideas ?
Thanks
CCB
May 28, 2004 at 6:59 am
This problem has been around for, oh, something like 40 years or so. One approach is to use a computed column in the order by clause on the second select. The effect is that you are now ordering on 001, 002, 011, 012, etc.
Good Luck
Wayne
This function will right justify and pad any text value:
IF exists (SELECT * from dbo.sysobjects
WHERE id = object_id(N'FN_LPAD'))
DROP FUNCTION FN_LPAD
GO
CREATE FUNCTION FN_LPAD (@InString varchar(7000),
@InPadChar varchar(1),
@InResultLength int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ResultString VARCHAR(8000),
@InStringLen int,
@i int
SET @InStringLen = LEN(RTRIM(@InString))
IF @InStringLen >= @InResultLength
BEGIN
SET @ResultString = RTRIM(@InString)
END
ELSE
BEGIN
SET @ResultString = ''
SET @i = 1
WHILE @i <= @InResultLength
BEGIN
SET @ResultString = @ResultString + @InPadChar
SET @i = @i + 1
END
SET @ResultString = STUFF(@ResultString, @InResultLength - @InStringLen + 1, @InStringLen, @InString)
END
-- SELECT @ResultString
RETURN @ResultString
END
May 28, 2004 at 7:10 am
select [desc]
from (
select Parentid,
0 as [key1],
0 as [key2],
Parentdesc as [desc]
from Ptable
union
select Parentid,
1 as [key1],
cast(Descriptionid as int) as [key2],
left(Descriptionid+space(4),4)+Descriptiondesc as [desc]
from Dtable
) a
order by a.Parentid,a.key1,a.key2
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply