SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help with query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

makimark
Starting Member

34 Posts

Posted - 10/04/2005 :  16:30:27  Show Profile  Reply with Quote
the query is simple but i'm battling with the output. Select * from table

I have this result

Surname Company
-------------------------------------------------- ------------
BLACK 2
Spinnler 3
Black 4
Spinnler 4

but need to get to this

Surname Company 1 Company 2
------------------- ------------ ---------
BLACK 2 4
Spinnler 3 4

Can i do this in a set based command without having to create another table and using a cursor (shudder) ?

Edited by - makimark on 10/04/2005 16:33:22

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/04/2005 :  17:32:19  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Check the answers at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53885

---------------------------
EmeraldCityDomains.com
Go to Top of Page

TestEngineer
Starting Member

USA
29 Posts

Posted - 12/15/2005 :  21:56:30  Show Profile  Reply with Quote
I have a similar issue as makimark, but I don't see how the topic 53885 answers the question.

I have a result set that looks like this:
ID Value Type
65 A 1
65 B 2
65 C 3
65 D 4
65 E 4
65 F 4
65 G 5
66 H 1
66 I 2
66 J 3
66 K 4
66 L 4
66 M 4
66 N 5
67 O 1
67 P 2
67 Q 3
67 R 4
67 S 4
67 T 4
67 U 5
68 V 1
.
.
.

I have 3 specific columns to fill (it will not vary). I'd like my table result to look like the following (given the above example information
ID Type1 Type2 Type3 Type4_0 Type4_1 Type4_2 Type5
65 A B C D E F G
66 H I J K L M N
67 O P Q R S T U
68 V . . .

I've figured I can do an INNER JOIN on the ID field and multiple sub-Select statements, but how the heck do you get Type 4 to match up like this?

If I was just going for one ID at a time, this would be easy, since I'd have only three rows to work with on the Type. This result is from another view select statement and I can get unique IDs for each value also. But since I need to return 100's of IDs in my result,I can't use Min and Max on that ID to get the result. I'm not trying to output to a csv, but send the information into a view result for a report.

Any assistance would be much appreciated. Thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/16/2005 :  01:42:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

or

Cross tab
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TestEngineer
Starting Member

USA
29 Posts

Posted - 12/16/2005 :  07:38:24  Show Profile  Reply with Quote
Thanks for the quick reply Madhivanan. The method described in the link, http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true, doesn't work for my application because it returns only 6 columns, ID, Type1, Type2, Type3, expr1, Type5 with expr1 showing all 3 values comma seperated for Type4. I need 3 distinct columns for each piece of data in Type4 (8 columns).

The second link doesn't seem to be valid. I did follow a link for cross tabs, but how do I get the columns without aggregate functions? I'm not looking to perform any functions on the column data, as each of these values is actually a unique serial number (I definitely don't want to add 1 to one of those).

I need to return a result set that has exactly 1/7th the number of records as the source table. So If I have 3500 records in my source table, I'll have 500 records in the table resulting from this operation.

Thanks again for the quick help.
Go to Top of Page

tocroi72
Yak Posting Veteran

89 Posts

Posted - 12/16/2005 :  16:01:49  Show Profile  Reply with Quote
hopefully this work for you.
create table #tmp (c1 varchar (50),c2 int)

--truncate table #tmp
insert into #tmp select 'Spinnler',4
insert into #tmp select 'Spinnler',3
insert into #tmp select 'BLACK',2
insert into #tmp select 'BLACK',4

set nocount on
declare @v1 nvarchar(50),@v2 nvarchar(50),@count integer,@rowcount integer, @whole nvarchar(100),@i integer
select top 1 @v1 = c1 from #tmp
set @count = @@rowcount
while @count <> 0
begin
set @i = 1
set @whole = ''
select @rowcount = count(*) from #tmp where c1 = @v1
while @i < @rowcount + 1
begin
select top 1 @v1 = c1,@v2 =c2 from #tmp where c1 = @v1
set @whole = @whole + ' ' + convert(varchar,@v2)
delete #tmp where c1 = @v1 and c2 = @V2
set @i = @i+1
end
print @v1 + ' ' + rtrim(ltrim(@whole))
Delete #tmp where c1 = @v1
select top 1 @v1 = c1 from #tmp
set @count = @@rowcount
end
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/16/2005 :  16:05:22  Show Profile  Reply with Quote
Again, you forgot the cursor

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000