Select Help! - 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)

Select Help!

Author

Topic

sorcir24
Starting Member

7 Posts

Posted - 2005-02-16 : 12:44:22

Hello All!

I need a little help to solve a Prob here!
I have the table products with
name
"carpet"
"socks"
"t-shits"
"bed"
"shoes"

and the select clause have to return this
name1 name2 name3
Carpet socks t-shirts
bed shoes

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 14:09:19

you basicaly want to split the data into 3 columns, right?
that would probably be simpler in the front end.
how much data do you have?

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 14:15:25

but if you must do it on the server this will do it:


declare @MainTable table (id int identity(1,1), name varchar(20))
insert into @MainTable (name)
select 'carpet' union all
select 'socks' union all
select 't-shirts' union all
select 'bed' union all
select 'shoes'

select 	*
from 	@MainTable

select 	identity(int, 1,1) as id, name
into 	#temp1
from 	@MainTable
where 	id%3=1

select 	identity(int, 1,1) as id, name
into 	#temp2
from 	@MainTable
where 	id%3=2

select 	identity(int, 1,1) as id, name
into 	#temp3
from 	@MainTable
where 	id%3=0

select 	t1.name as name1, isnull(t3.name, '') as name2, isnull(t3.name, '') as name3
from 	#temp1 t1 
	left join #temp2 t2 on t1.id = t2.id
	left join #temp3 t3 on t1.id = t3.id

drop table #temp1
drop table #temp2
drop table #temp3

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 14:34:14

[code]
declare @MainTable table (id int identity(1,1), name varchar(20))
insert into @MainTable (name)
select 'carpet' union all
select 'socks' union all
select 't-shirts' union all
select 'bed' union all
select 'shoes'

select *
from @MainTable

Select *
From (Select distinct id = (Id-1)/3 From @mainTable) A
Left Join @mainTable B
On (A.id*3)+0 = B.id-1
Left Join @mainTable C
On (A.id*3)+1 = C.id-1
Left Join @mainTable D
On (A.id*3)+2 = D.id-1
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

sorcir24
Starting Member

7 Posts

Posted - 2005-02-16 : 16:46:13

Thank you all!
but i´m sorry, the statement of declare @maintable table(etc) does not work in SQL Server 7!
Spirit, do you know another method?
Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 17:03:15

This should work for Sql Sevrer 7... though I can't test it


Create Table MainTable (id int identity(1,1), name varchar(20))
insert into MainTable (name)
select 'carpet' union all
select 'socks' union all
select 't-shirts' union all
select 'bed' union all
select 'shoes'

select 	*
from 	MainTable


Select *
From (Select distinct id = (Id-1)/3 From mainTable) A
Left Join mainTable B
On (A.id*3)+0 = B.id-1
Left Join mainTable C
On (A.id*3)+1 = C.id-1
Left Join mainTable D
On (A.id*3)+2 = D.id-1

Drop Table mainTable

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 19:10:23

@maintable is irrelevant to your situation. you should replace @maintable with you table.
me and corey used it just for examples.

Go with the flow & have fun! Else fight the flow

sorcir24
Starting Member

7 Posts

Posted - 2005-02-17 : 13:06:42

Spirit and Seventhnight
thank you for you help! :) ;)

sorcir24
Starting Member

7 Posts

Posted - 2005-02-17 : 13:52:27

Ok i have anothe one!
i have the table details

PN SN
AA 011
AA 021
BB 041
BB 010
BB 011
BB 014
BB 016
BB 024
CC 014
CC 021
CC 048
And i want to get
PN SN1 SN2 SN3 SN4
AA 011 021
BB 041 010 011 014
BB 016 024
CC 014 021 048

I was really trying hard with the method that you post here but i don´t get it!
Please!!
Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-17 : 14:10:34

[code]
Create Table MainTable (id int identity(1,1), PN varchar(10), SN varchar(10), seqn int)
insert into MainTable (PN,SN)
select 'AA', '011' union all
select 'AA', '021' union all
select 'BB', '041' union all
select 'BB', '010' union all
select 'BB', '014' union all
select 'BB', '016' union all
select 'BB', '024' union all
select 'CC', '014' union all
select 'CC', '021' union all
select 'CC', '048'

Update MainTable set seqn = (Select count(*) From MainTable A Where PN = MainTable.PN and id < MainTable.id) from MainTable

select *
from MainTable

Select A.PN, SN1 = B.SN, SN2 = C.SN, SN3 = D.SN, SN4 = E.SN
From (select distinct seqn = seqn/4, PN from MainTable) A
Left Join mainTable B
On A.PN = B.PN
and (A.seqn*4)+0 = B.seqn
Left Join mainTable C
On A.PN = C.PN
and (A.seqn*4)+1 = C.seqn
Left Join mainTable D
On A.PN = D.PN
and (A.seqn*4)+2 = D.seqn
Left Join mainTable E
On A.PN = E.PN
and (A.seqn*4)+3 = E.seqn
Order By A.PN, B.SN, C.SN, D.SN

Drop Table mainTable
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

sorcir24
Starting Member

7 Posts

Posted - 2005-02-17 : 15:50:00

Man...
you are a genius! Thank you!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-17 : 16:26:32

Not sure about that... but you're welcome

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain