SQL AddNew Column - SQL Server Forums

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

SQL Server Development (2000)

SQL AddNew Column

Author

Topic

cheatasp
Starting Member

10 Posts

Posted - 2009-05-21 : 23:39:36

Dear All,

I have open problem with query

SELECT '*' + REPLACE(AssetCode,'-','') + '*' FROM TAB_Asset

it will return 1 column and 3 rows, but I want to show 3 columns and 1 row.

Please help me.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:16:52

so you want cross tabbed result? can you illustrate what all values you want to cross tab with an example?

cheatasp
Starting Member

10 Posts

Posted - 2009-05-22 : 04:39:51

Dear

Now I got my result already, I use temp table like this

CREATE TABLE TPR_Temp
(
Ass_ID int,
AssetCode1 nvarchar(16) NULL,
AssetCode2 nvarchar(16) NULL,
AssetCode3 nvarchar(16)NULL,
)

Declare @AssetCode nvarchar(16)
Declare @i int
Declare @id int
set @id=1
set @i=0
Declare c cursor for sELECT '*' + replace(AssetCode,'-','')+'*' FROM TAB_Asset
open c
fetch c into @AssetCode
while(@@FETCH_STATUS=0)
begin
if(@i=0)
begin
insert into tpr_temp(Ass_ID,AssetCode1) values(@id,@AssetCode)
end
else if(@i=1)
begin
update tpr_temp set AssetCode2=@AssetCode where Ass_ID=@id
end
else if(@i=2)
begin
update tpr_temp set AssetCode3=@AssetCode where Ass_ID=@id
set @id=@id+1
set @i=-1
end
set @i=@i+1
print @AssetCode
fetch c into @AssetCode

end
Deallocate c
close c

Select * from TPR_Temp
Drop TABLE TPR_Temp

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources