Column to Row - 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

Import/Export (DTS) and Replication (2000)

Column to Row

Author

Topic

SQL_DEV
Starting Member

1 Post

Posted - 2007-07-27 : 10:59:19

need suggestions to this problem
Data is like this(WHEN RETURNING FROM DIFFERENT TABLES)
column A column B

7001 NULL
7002A A
7002AB AB
7003VA VA
7003AV AV
7004ABC ABC
7005XYZ XYZ

NEED TO CONVERT TO LOOK LIKE THIS FOR FUTURE COMBINATIONS

column A column B
7001 NULL
7002A A
7002AB A
7002AB B
7003VA V
7003VA A
7003AV A
7003AV V
7004ABC A
7004ABC B
7004ABC C

ANY HELP WILL BE HELPFUL

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-27 : 13:31:19

--Test Data
Declare @table1 table (colA varchar(20))
Insert Into @table1 Values ('7001')
Insert Into @table1 Values ('7002A')
Insert Into @table1 Values ('7002AB')

Declare @table2 table (colA varchar(20), colB varchar(20))
Insert Into @table2 values ('7002A', 'A')
Insert Into @table2 values ('7002AB', 'AB')

--
Declare @TempTable table (colA varchar(20), colB varchar(20))

Declare @colA varchar(20), @colB varchar(20)

Select Top 1 @colA = colA
, @colB = colB
From @table2
Order By colA

While @@RowCount <> 0
Begin
While Len(@colB) > 0
Begin
Insert Into @TempTable Values (@colA, Left(@colB, 1))
Set @colB = Right(@colB, Len(@colB) - 1)
End

Select Top 1 @colA = colA
, @colB = colB
From @table2
Where colA > @colA
Order By colA
End

Select t1.colA, TT.colB
From @table1 t1
Left Outer Join @TempTable TT On T1.colA = TT.colA

tiheld
Starting Member

1 Post

Posted - 2007-07-27 : 15:35:38

declare @test table (
id nvarchar(7) primary key,
val nvarchar(4),
parsed bit default 0
)

declare @parsed table (
id nvarchar(7) not null,
val nvarchar(1)
)

declare @currentid nvarchar(7)
declare @currentval nvarchar(4)

insert into @test (id,val) values('7001', NULL)
insert into @test (id,val) values('7002A', 'A')
insert into @test(id,val) values('7002AB', 'AB')
insert into @test (id,val) values('7003VA', 'VA')
insert into @test (id,val) values('7003AV', 'AV')
insert into @test (id,val) values('7004ABC', 'ABC')
insert into @test (id,val) values('7005XYZ', 'XYZ')

select top 1 @currentid = id, @currentval =val from @test

while @currentid is not null
Begin
if @currentval is null
insert into @parsed values(@currentid, null)
else
begin
while @currentval is not null and len(@currentval) > 0
begin
insert into @parsed (id, val)
values(@currentid, substring(@currentval, 1,1))

select @currentval = substring(@currentval, 2, len(@currentval) -1)
end
end

update @test set parsed = 1 where id = @currentid

set @currentid = null
select top 1 @currentid = id, @currentval =val from @test where parsed = 0

End

select * from @parsed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:48:24

No need for looping. Use set-based method

-- Prepare sample data
DECLARE	@Sample TABLE (A VARCHAR(7), B VARCHAR(3))

INSERT	@Sample
SELECT	'7001', NULL UNION ALL
SELECT	'7002A', 'A' UNION ALL
SELECT	'7002AB', 'AB' UNION ALL
SELECT	'7003VA', 'VA' UNION ALL
SELECT	'7003AV', 'AV' UNION ALL
SELECT	'7004ABC', 'ABC' UNION ALL
SELECT	'7005XYZ', 'XYZ'

-- Show the expected output
SELECT		s.A,
		SUBSTRING(s.B, v.Number, 1) AS NewB
FROM		@Sample AS s
CROSS JOIN	master..spt_values AS v
WHERE		v.Number > 0
		AND v.Number <= LEN(ISNULL(s.B, '_'))
		AND v.Type = 'p'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources