query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-24 : 07:53:20

dear all,

my result is like this
218 1 4949 80864
272 2 4207 77781
i want result to be like this below

100 1 4949 80864
100 1 4949 80864
18 1 4949 80864
100 2 4207 77781
100 2 4207 77781
72 2 4207 77781

plz anybody advice

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-24 : 07:59:36

What version of sql server you are using ?

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-24 : 08:00:40

2008 r2

Devart
Posting Yak Master

102 Posts

Posted - 2010-12-24 : 08:38:29

Hello,

For example:

create table t_test(id1 int,id2 int,id3 int,id4 int);
insert into t_test values(218,1,4949,80864);
insert into t_test values(272,2,4207,77781);

go

WITH _test AS
(
SELECT id1 AS id1,id2,id3,id4 FROM t_test
UNION ALL
SELECT id1-100 AS id1,id2,id3,id4 FROM _test
WHERE id1-100>0
)

SELECT
CASE WHEN id1<100 THEN id1 ELSE 100 END AS id1,
id2,id3,id4
FROM _test
ORDER BY id2,id1 DESC;

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-27 : 00:19:51

thanks devart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources