June 28, 2010 at 12:25 am
How to select this
ID
--
1
2
3
4
5
6
and return it as :
id1 id2
--- ---
1 2
3 4
5 6
Thanks
June 28, 2010 at 12:41 am
Are those the only columns? :hehe:
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
June 28, 2010 at 12:44 am
yes
June 28, 2010 at 1:25 am
Can you show us the code you have tried so far , and where you are having difficulties?
June 28, 2010 at 2:23 am
Jaybada (6/28/2010)
How to select thisID
--
1
2
3
4
5
6
and return it as :
id1 id2
--- ---
1 2
3 4
5 6
Thanks
June 28, 2010 at 3:51 am
Hi again,
Is this some kind of assignment?
DECLARE @tbl TABLE (ID INT)
DECLARE @value NVARCHAR(MAX), @isEven BIT, @half INT
INSERT INTO @tbl
VALUES (1),(2),(3),(4),(5),(6)--,(7)
--TRY ADDING 7 OR MORE NUMEBRS
-------------------------------------------------------------
-- Proper approach (I think)
-- THIS IS FOR ODD NUMBERS
SELECT @half=CASE
WHEN COUNT(ID)/2=CAST(COUNT(ID) AS DECIMAL(4,1))/2
THEN COUNT(ID)/2
ELSE COUNT(ID)/2+1
END
FROM @tbl
SELECT TOP (@half) t1.ID,t2.ID
FROM @tbl t1
LEFT OUTER JOIN @tbl t2 ON (t1.ID+@half=t2.ID)
ORDER BY t1.ID
-------------------------------------------------------------
--CHEAT
SELECT TOP (3) t1.ID,t2.ID
FROM @tbl t1
LEFT OUTER JOIN @tbl t2 ON (t1.ID+3=t2.ID)
ORDER BY t1.ID
-------------------------------------------------------------
--CHEAT SOME MORE
SELECT TOP 3 ID, ID+3
FROM @tbl
ORDER BY ID
Well, this was only according to the given problem.
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
June 28, 2010 at 3:56 am
June 28, 2010 at 4:07 am
THANKS, GUYS , LET ME TRY YOUR SUGGESTIONS.
June 28, 2010 at 9:03 pm
aw lol, stupid of me, don't mind my example, I misread your sample given :hehe:
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
June 28, 2010 at 10:28 pm
And if there are any gaps, then this will work for you:
DECLARE @test-2 TABLE (ID INT)
INSERT INTO @test-2
VALUES (1),(2),(3),(4),(5),(6)
;WITH CTE AS
(
SELECT ID, RN = ROW_NUMBER() OVER (ORDER BY ID)
FROM @test-2
)
select t1.RN as Id1, t2.RN as Id2
from CTE t1 left join CTE t2 on t2.RN = t1.RN +1
where t1.RN%2 != 0
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 29, 2010 at 8:54 am
;WITH s as (SELECT id=row_number()over(order by (select 0))
FROM(SELECT 1a,1b,1c,1d,1z,1f,1g,1h,1i,1j,1k)E
UNPIVOT(N FOR x IN(a,b,c,d,z,f,g,h,i,j,k))u)
,s2 as (SELECT i=row_number()over(order by (select 0))
FROM(SELECT 1a,1b)E UNPIVOT(N FOR x IN(a,b))u)
, c as (select id=c+1 from (select c=COUNT(*) from s)c where c%2=1)
select id1=MIN(id), id2=case max(r) when 1 then null else MAX(id) end
from
(
select id,f=id+i,r
from (
select id,r=0 from s
union all
select id,r=1 from c
)s1
cross join s2
)x
group by f
having COUNT(*) > 1 AND MIN(id) % 2 = 1:-):-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply