Sql question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sql question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-21 : 22:44:50

bos Appu writes "I have a table test

Test
---
Col1
2
14
1
23
11

I want to write a select statement to return

Col1 Col2
2 14
14 1
1 23
23 11"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 01:17:17

Where do you want to show the data like these?
Here is one of the methods


Declare @t table(i int identity,col int)
Insert into @t values(2)
Insert into @t values(14)
Insert into @t values(1)
Insert into @t values(23)
Insert into @t values(11)

Declare @t1 table(col1 int,col2 int)

Insert into @t1 Select t1.col as col1,t2.col as col2 from @t t1 inner join @t t2 on t1.i<t2.i 

select Distinct col1,(select top 1 col2 from @t1 where col1=T.col1) from @t1 T

Madhivanan

Failing to plan is Planning to fail

bosappu
Starting Member

1 Post

Posted - 2005-09-22 : 19:29:53

Thanks for your solution. It is easy to do in two statements. I want it to be done in one Sql Stattement. Only one Select Query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 00:47:11

I dont know how to do it in single query
Wait until Gurus reply to it

Madhivanan

Failing to plan is Planning to fail

Shoaib
Starting Member

4 Posts

Posted - 2005-09-23 : 06:45:04

The query runs in SQL server 2000

select cola, col1 from
(
select cola, count(colb)as colb from
(select top 5 col as cola,'1' as colb from test
union all
select col,'1' from test
where col not in
(select top 1 col from test)
union all
select col,'1' from test
where col not in
(select top 2 col from test)
union all
select col,'1' from test
where col not in
(select top 3 col from test)
union all
select col,'1' from test
where col not in
(select top 4 col from test)
) as xyz
group by cola
)as xyz1
inner join
(
select col1,count(col2) as col2 from
(
select top 5 col as col1,'1' as col2 from test
union all
select col,'1' from test
where col not in
(select top 1 col from test)
union all
select col,'1' from test
where col not in
(select top 2 col from test)
union all
select col,'1' from test
where col not in
(select top 3 col from test)
union all
select col,'1' from test
where col not in
(select top 4 col from test)
) as abc
group by col1
) as abc1
on cola <> col1
where colb = col2-1
order by colb

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 06:59:31

Well. If you have duplicate value then you will have problem


Declare @t table(i int identity,col int)
Insert into @t values(2)
Insert into @t values(14)
Insert into @t values(1)
Insert into @t values(23)
Insert into @t values(11)
Insert into @t values(2)

My query's result

and yours is

Madhivanan

Failing to plan is Planning to fail

Shoaib
Starting Member

4 Posts

Posted - 2005-09-23 : 07:16:02

The query I posted , works for

Table : Test
Column Name of Table Test : Col (not col1) as demanded
OutPut Column Names (cola , col1) not (col1, col2) as demanded

Sorry for no indentation

The query is generic for 5 members only

Shoaib,
Pakistan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 07:27:10

Well

Madhivanan

Failing to plan is Planning to fail

Shoaib
Starting Member

4 Posts

Posted - 2005-09-23 : 07:57:28

Yes , for duplicate values , Query doesn't seem to work. I will try to correct it soon. But I think that what is required is "order" too.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 09:12:21

>>But I think that what is required is "order" too.

Yes I know that
Probably it can be done with Order by Case when...end

Madhivanan

Failing to plan is Planning to fail

Shoaib
Starting Member

4 Posts

Posted - 2005-09-27 : 05:52:15

Try this query.
Again it is generic for 5 items in Test
But it has no duplicate values problem as was in previous query posted by me.

Query runs on SQL server 2000

Regards
Shoaib
--------------------------------------------------------------------

select col1,col2 from
(
select top 1 col as col1, 1 as pos1 from test
union all
select val2-val1, pos2 from
(select sum(col)as val1,1 as pos1 from
(select top 1 col from test) as abvc
union
select sum(col),2 from
(select top 2 col from test) as abvc
union
select sum(col),3 from
(
select top 3 col from test
) as abvc
union
select sum(col),4 from
(
select top 4 col from test
) as abvc
union
select sum(col),5 from
(
select top 5 col from test
) as abvc

) as a1

inner join
(
select sum(col)as val2,1 as pos2 from
(
select top 1 col from test
) as abvc
union
select sum(col),2 from
(
select top 2 col from test
) as abvc
union
select sum(col),3 from
(
select top 3 col from test
) as abvc
union
select sum(col),4 from
(
select top 4 col from test
) as abvc
union
select sum(col),5 from
(
select top 5 col from test
) as abvc
) as a2
on pos2 = pos1 +1
) as t1
inner join
(
select top 1 col as col2, 1 as pos2 from test
union all
select val2-val1, pos2 from
(select sum(col)as val1,1 as pos1 from
(select top 1 col from test) as abvc
union
select sum(col),2 from
(select top 2 col from test) as abvc
union
select sum(col),3 from
(
select top 3 col from test
) as abvc
union
select sum(col),4 from
(
select top 4 col from test
) as abvc
union
select sum(col),5 from
(
select top 5 col from test
) as abvc

) as a1

inner join
(
select sum(col)as val2,1 as pos2 from
(
select top 1 col from test
) as abvc
union
select sum(col),2 from
(
select top 2 col from test
) as abvc
union
select sum(col),3 from
(
select top 3 col from test
) as abvc
union
select sum(col),4 from
(
select top 4 col from test
) as abvc
union
select sum(col),5 from
(
select top 5 col from test
) as abvc
) as a2
on pos2 = pos1 +1
) as t2
on t1.pos1 = t2.pos2 -1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 06:03:47

Well. I appreciate your effort

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources