How can i do that!! - 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 2005 Forums

Transact-SQL (2005)

How can i do that!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-12 : 09:24:01

Jackbee writes "I have a table :
table A (
articleId int,
articleName nvarchar(400)
)
How can i use select statement or do someway to select a result with the format below:

articleId1,articleName1,articleId2,articleName2,articleId3,articleName3

ex:
A:
articleId articleName
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6
7 a7

and i want the result is :
B:
id1 name1 id2 name2 id3 name3
1 a1 2 a2 3 a3
4 a4 5 a5 6 a6
7 a7

i have use a temporary table to do this but i think this cost a lost of time and make my application slow down,are there anyway to make this with select statement , inner join or some functions in sql 2000 or 2005 , please help me , thank"

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-03-12 : 09:38:46

Check this link
http://ryanfarley.com/blog/archive/2005/02/17/1712.aspx

Hope it helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-12 : 13:05:58

[code]-- prepare sample data
declare @t table (articleid int, articlename varchar(2))

insert @t
select 1, 'a1' union all
select 2, 'a2' union all
select 3, 'a3' union all
select 4, 'a4' union all
select 5, 'a5' union all
select 6, 'a6' union all
select 7, 'a7'

-- show the expected output
select max(case when recid % 3 = 0 then articleid end) as id1,
max(case when recid % 3 = 0 then articlename end)as name1,
max(case when recid % 3 = 1 then articleid end)as id2,
max(case when recid % 3 = 1 then articlename end)as name2,
max(case when recid % 3 = 2 then articleid end)as id3,
max(case when recid % 3 = 2 then articlename end)as name3
from (
select articleid,
articlename,
row_number() over (order by articleid) - 1 as recid
from @t
) as x
group by recid / 3[/code]

Peter Larsson
Helsingborg, Sweden

jackbee
Starting Member

5 Posts

Posted - 2007-03-13 : 00:03:42

Thnks for everyone help me, it's very userfull for me, but i've just wonder , if i have more than 7 rows in original table i have to use while..do to insert into temporary table so it make the sql slow down or not ???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 00:17:16

quote:
Originally posted by jackbee

Thnks for everyone help me, it's very userfull for me, but i've just wonder , if i have more than 7 rows in original table i have to use while..do to insert into temporary table so it make the sql slow down or not ???

You mean the declare @t . . . insert @t part ?
That is only for demonstrating how the query work.

You only required the from the section "show the expected output" onwards and replace @t with your actual table name

jackbee
Starting Member

5 Posts

Posted - 2007-03-13 : 00:39:59

beat me !! ;p

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 00:44:46