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.
| 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 tableI have this resultSurname Company-------------------------------------------------- ------------BLACK 2Spinnler 3Black 4Spinnler 4but need to get to thisSurname Company 1 Company 2 ------------------- ------------ ---------BLACK 2 4Spinnler 3 4Can 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 |
|
|
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 Type65 A 165 B 265 C 365 D 465 E 465 F 465 G 566 H 166 I 266 J 366 K 466 L 466 M 466 N 567 O 167 P 267 Q 367 R 467 S 467 T 467 U 568 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 informationID Type1 Type2 Type3 Type4_0 Type4_1 Type4_2 Type565 A B C D E F G66 H I J K L M N67 O P Q R S T U68 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
22864 Posts |
|
|
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. |
|
|
|
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 #tmpinsert into #tmp select 'Spinnler',4insert into #tmp select 'Spinnler',3insert into #tmp select 'BLACK',2insert into #tmp select 'BLACK',4set nocount ondeclare @v1 nvarchar(50),@v2 nvarchar(50),@count integer,@rowcount integer, @whole nvarchar(100),@i integerselect top 1 @v1 = c1 from #tmpset @count = @@rowcountwhile @count <> 0 beginset @i = 1set @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 = @v1select top 1 @v1 = c1 from #tmpset @count = @@rowcountend |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Help with query