How to select 1 column into different rows and columns

Question

How to select 1 column into different rows and columns

Jaybada

SSC Rookie

Points: 31
More actions
June 28, 2010 at 12:25 am

#236972

How to select this
ID
--
1
2
3
4
5
6
and return it as :
id1 id2
--- ---
1 2
3 4
5 6
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

Are those the only columns? :hehe:

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Jaybada SSC Rookie Points: 31 More actions · Answer 2

Jaybada

SSC Rookie

Points: 31

June 28, 2010 at 12:44 am

#1185541

yes

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Can you show us the code you have tried so far , and where you are having difficulties?

Clear Sky SQL
My Blog[/url]

aa 71562 Grasshopper Points: 11 More actions · Answer 4

Jaybada (6/28/2010)
How to select this
ID
--
1
2
3
4
5
6
and return it as :
id1 id2
--- ---
1 2
3 4
5 6
Thanks

Quatrei.X SSCrazy Points: 2697 More actions · Answer 5

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!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

If your Id's are always increases by 1 and you have no gaps, you can use this:

select t1.ID as Id1, t2.ID as Id2

from @test-2 t1 left join @test-2 t2 on t2.ID = t1.ID +1

where t1.ID%2 != 0

Please note, the above will only work if you have NO gaps in your Ids (as in your sample)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jaybada SSC Rookie Points: 31 More actions · Answer 7

Jaybada

SSC Rookie

Points: 31

June 28, 2010 at 4:07 am

#1185617

THANKS, GUYS , LET ME TRY YOUR SUGGESTIONS.

Quatrei.X SSCrazy Points: 2697 More actions · Answer 8

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!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

WayneS SSC Guru Points: 95460 More actions · Answer 9

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Victor Dvoryaninov SSC Veteran Points: 208 More actions · Answer 10

;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:-):-)