Results 1 to 2 of 2

Thread: SQL Problme

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    SQL Problme

    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

  2. #2
    Join Date
    Aug 2007
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •