Split single column of data into 2 columns

[BeachSandGuy]

Hi,
I am preparing a sql query to put into an rdl report for SQL Server. I have data which I would like to show up in two columns (for printing purposes).
Table looks like this:
SiteCode | Description
BU | Buffalo
DU | Duluth
MI | Miami
PH | Phoenix

I'd like to write a query so the results would look like this:
SiteCode | Description | SiteCode | Description
BU | Buffalo | MI | Miami
DU | Duluth | PH | Phoenix

I've added another column to indicate column like so:
SiteCode | Description | Column
BU | Buffalo | 1
DU | Duluth | 1
MI | Miami | 2
PH | Phoenix | 2

And then
Select
Case when Column=1 Then SiteCode Else Null End as 'Col 1',
Case when Column=2 Then SiteCode Else Null End as 'Col 2'
From Table

It works except I have a bunch of nulls,
Col 1 | Col 2
BU | Null
DU | Null
Null | MI
Null | PH

Any suggestions?
As always Thank you for taking the time, this is a great forum, searched, couldn't find a similar issue.

Ian

 

[markros]

Please check this thread - it covers your exact requirement, I believe

http://forums.asp.net/p/1442148/3365156.aspx#3365156

 

[markros]

BTW, I think you want the opposite of split - you want to concatenate, not split

 

[RyanEK]

Hmmm this is an interesting requirement, probably one not best tackled using TSQL. Ideally you should use a frontend to format the display.

Is the example you provided just a sample of the entire data? ie. are there more sites?

How do you determine which sites go into which column?

 

[RyanEK]

Not sure if this is what you want, but for a possible solution you could do the folowing.

You'll need an index for the records. Can't use row_number() since there is nothing to sort by so

declare @test table (SiteCode varchar(2), Description varchar(10), col int)
insert into @test values ('BU', 'Buffalo', 1)
insert into @test values ('DU', 'Duluth', 1)
insert into @test values ('MI', 'Miami', 2)
insert into @test values ('PH', 'Phoenix', 2)
insert into @test values ('SY', 'Sydney', 1)
insert into @test values ('MB', 'Melb', 1)
insert into @test values ('PR', 'Perth', 2)
insert into @test values ('AD', 'Adel', 2)

declare @table table (indx int identity(0,1), SiteCode varchar(2), Description varchar(10), col int)
insert into @table (sitecode, description, col)
select * from @test

You now have @table with an index. You can then create a temp table and populate the first column

declare @final table (indx int, Col1 varchar(2), Col2 varchar(10))
insert into @final (indx, Col1)
select indx, sitecode 
from @table where col = 1

Then populate the 2nd column

update f
set f.Col2 = t.sitecode
from @final f
join @table t on t.indx = f.indx + 2

You should end up with

0	BU	MI
1	DU	PH
4	SY	PR
5	MB	AD

Ryan