| Author |
 Topic  |
|
|
makimark
Starting Member
34 Posts |
 Posted - 10/04/2005 : 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) ? |
Edited by - makimark on 10/04/2005 16:33:22
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
|
|
TestEngineer
Starting Member
USA
29 Posts |
Posted - 12/15/2005 : 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
|
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
TestEngineer
Starting Member
USA
29 Posts |
Posted - 12/16/2005 : 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. |
|
|
|
tocroi72
Yak Posting Veteran
89 Posts |
Posted - 12/16/2005 : 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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
| |
Topic |
|
Help with query
