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)
 Help with query

Author  Topic 

makimark
Starting Member

34 Posts

Posted - 2005-10-04 : 16:30:27
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) ?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-04 : 17:32:19
Check the answers at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53885

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

TestEngineer
Starting Member

29 Posts

Posted - 2005-12-15 : 21:56:30
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

22864 Posts

Posted - 2005-12-16 : 01:42:09
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

29 Posts

Posted - 2005-12-16 : 07:38:24
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 - 2005-12-16 : 16:01:49
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 - 2005-12-16 : 16:05:22
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
   

- Advertisement -