July 16, 2010 at 9:28 am
hi ,
i have one tables and there is two columns Like this ones.
create table test1(createid int ,createrecord varchar(20)). i inserted some data in this table then after looks like a
12
15
18
19
29
25
27
37
39
but i want to display the records like
1 2
5
8
9
2 9
5
7
3 7
9
can we display the records like above senario.If its possible then please five me fast reply.I will very happy when i will get respons. If u need any clarification please leet me know.Thanks In advance!!!!
Regards
Bharat
July 16, 2010 at 10:13 am
Why would you do such formatting server side and not client side? Stuff like this usually should be done by the frontend app.
But if you're forced to do it, look for ROW_NUMBER() in BOL (BooksOnLine, the SQL Server help system). Based on the row number you could use a CASE statement to either show col1 or blank. But like I said, I wouldn't recommend it...
July 16, 2010 at 10:26 am
Thanks for the good suggestion!!!!can you write the query for that scenario.i will be very happy if you do for me.Thanks
Regards
Bharat
July 16, 2010 at 10:52 am
singhbharat321 (7/16/2010)
Thanks for the good suggestion!!!!can you write the query for that scenario.i will be very happy if you do for me.ThanksRegards
Bharat
Please provide table def, sample data and expected result in a ready to use format as described in the first link in my signature.
July 16, 2010 at 11:07 am
hi..thanks for the good response.
this is my table i have created
create table test1(createid int ,createrecord varchar(20))
and after that i inserted some data in this field.
insert into test1 values (3,9)
insert into test1 values(3,8)
insert into test1 values(2,8)
insert into test1 values(2,4)
insert into test1 values(2,5)
insert into test1 values(2,7)
insert into test1 values(1,1)
insert into test1 values(1,2)
insert into test1 values(1,8)
insert into test1 values(1,3)
after insertion of data i got this types of data
select * from test1
38
28
24
29
25
27
37
11
18
13
but want to display this column likes below ones
1 1
8
3
2 8
4
9
5
7
3 7
8
thanks !!!
bharat
July 16, 2010 at 2:31 pm
The way you have this set up, you can't ever be guaranteed to get the output you want. Based on the output, my assumption is that you want the rows back in the order you entered them. If there is no column that can be used in an 'order by' to retrieve the rows in the entry order, the server can return them in any way that works best for the optimizer. You need to re-think your design to include a column that can be used to order the rows as you want them.
July 16, 2010 at 3:08 pm
Here's the approach I would use.
Please note that I had to add an identity column to the source table just like David suggested. In my first reply I assumed the order would be defined by sorting createrecord asc. But when re-reading your sample data I admit I've overlooked the obvious.
DECLARE @test1 TABLE (id INT IDENTITY(1,1),createid INT ,createrecord VARCHAR(20))
INSERT INTO @test1 VALUES (3,9)
INSERT INTO @test1 VALUES(3,8)
INSERT INTO @test1 VALUES(2,8)
INSERT INTO @test1 VALUES(2,4)
INSERT INTO @test1 VALUES(2,5)
INSERT INTO @test1 VALUES(2,7)
INSERT INTO @test1 VALUES(1,1)
INSERT INTO @test1 VALUES(1,2)
INSERT INTO @test1 VALUES(1,8)
INSERT INTO @test1 VALUES(1,3)
;
WITH cte AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY createid ORDER BY id ) AS ROW
FROM @test1
)
SELECT
CASE WHEN ROW=1 THEN CAST(createid AS VARCHAR(10)) ELSE '' END,
createrecord
FROM cte
ORDER BY createid,id
July 18, 2010 at 6:48 am
Thanks Lutz and David..
What i used to for this type case so i am explaining ...this thing i done but i am not very expert in database side.If i am right then no issue for me.this is the syntax i used for the above senario.if need any modification please let me know.Thanks
select * into #temp from test1 where 1 =0;
go
declare @previousvalue int;
declare @createid int;
declare @createrecord varchar(20);
DECLARE my_cur CURSOR FOR
select createid,createrecord from test1
order by createid;
OPEN my_cur;
FETCH NEXT FROM my_cur
INTO @createid,@createrecord;
WHILE @@FETCH_STATUS = 0
BEGIN
if @previousvalue = @createid
begin
insert into #temp values(null,@createrecord)
end
else
begin
insert into #temp values(@createid,@createrecord)
end
print @previousvalue;
set @previousvalue = @createid;
FETCH NEXT FROM my_cur
INTO @createid,@createrecord;
END
CLOSE my_cur;
DEALLOCATE my_cur;
go
select * from #temp
go
drop table #temp;
go
Regards
bharat
July 18, 2010 at 7:32 am
Your c.u.r.s.o.r (*cough*) suffers the same problem David and myself were talking about: there is absolutely no guarantee to get the rows back in the same order as the rows were entered. The only sorting operation you do is to order by createid.
With your approach the effect is exactly the same as if you'd use my approach without the additional id column I added. The only difference: the more rows you want to process the slower your approach will run since you touch each and every row (also called RBAR = row-by-agonizing-row).
If the order of @createrecord must be returned in the same order as inserted, you need an additional column. No way around that.
July 18, 2010 at 12:43 pm
Lutz's solution will be far faster than the cursor and will ensure the rows are returned correctly. The SQL Server isn't required to give the rows back to you in the order you entered them, so your code may work for a while and then break unexpectedly. As database tables grow and fragment, the optimizer may find it more efficient to return rows in something other than entry order. In a relational database, the way to guarantee order in a result set is to use the 'order by' clause in the select statement. If there is nothing to use in an 'order by' clause, the server will return the rows in the most efficient manner, not necessarily in entry order. You need another column that preserves the order you need and allows the server to return the rows in that order.
July 19, 2010 at 6:40 am
This looks like id, parent_id to me. If so, you just need to use a recursive script.
http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/
July 19, 2010 at 10:32 am
Hi
Thanks for great response.i agree with your statement.Thanks a lots.
Regards
Bharat
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply