SQL Problme

[lrb42]

Hi ,
I have following data in my table

P | A

AA |PP QQ RR
BB | FF EE GG

I want my result to look like this

AA |PP
AA |QQ
AA |RR
BB |FF
BB |EE
BB |GG


Can any body please tell me how to do this.

Thanks for your help.
Laxmikant

[steenbt]

here's a solution

create FUNCTION [dbo].[stringSplit]
(
@stringToSplit nvarchar(1000),
@separator nchar(1)
)
RETURNS @strings TABLE (string nvarchar(20) not null)
AS
begin
declare @tempStr nvarchar(20),
@pos int;

set @stringToSplit = ltrim(rtrim(@stringToSplit))

while @stringToSplit is not null and @stringToSplit != ''
begin
set @pos = charindex(@separator, @stringToSplit)
if @pos != 0
begin
select @tempStr = substring(@stringToSplit, 1, @pos - 1), @stringToSplit = ltrim(right(@stringToSplit, len(@stringToSplit) - @pos))
insert into @strings values(@tempStr)
end
else
begin
insert into @strings values(@stringToSplit)
set @stringToSplit = ''
end
end
return
end

create table #p (pData nvarchar(2) primary key not null)
create table #a (pData nvarchar(2) not null, aData nvarchar(50) not null)

insert into #p values('AA')
insert into #p values('BB')

insert into #a values('AA', 'PP QQ RR')
insert into #a values('BB', 'FF EE GG')

select #p.pData, s.string
from #p
inner join #a on #a.pData = #p.pData
cross apply dbo.stringSplit(aData, ' ' ) s

drop table #a
drop table #p