Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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
-------------------------------------------------- ------------
Spinnler 3
Black 4
Spinnler 4

but need to get to this

Surname Company 1 Company 2
------------------- ------------ ---------
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

SQL Slashing Gunting Master

3246 Posts

Posted - 10/04/2005 :  17:32:19  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Check the answers at

Go to Top of Page

Starting Member

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

Premature Yak Congratulator

22864 Posts

Posted - 12/16/2005 :  01:42:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote


Cross tab


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

Starting Member

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,, 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

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
set @i = 1
set @whole = ''
select @rowcount = count(*) from #tmp where c1 = @v1
while @i < @rowcount + 1
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
print @v1 + ' ' + rtrim(ltrim(@whole))
Delete #tmp where c1 = @v1
select top 1 @v1 = c1 from #tmp
set @count = @@rowcount
Go to Top of Page

Not Just a Number

15586 Posts

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



Hint: Want your questions answered fast? Follow the direction in this link

Add yourself!
Go to Top of Page
  Previous Topic Topic Next 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.04 seconds. Powered By: Snitz Forums 2000