Results 1 to 12 of 12

Thread: SQL Problem

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    2

    Unanswered: SQL Problem

    Hi All

    I have this problem !!!!!

    I want to create one record for multiple records in SQL 7.0. Example.

    I have multiple rows in a table with say following data

    Key Field1
    234 ab1
    234 ab2
    234 ab5
    235 ab1
    235 ab2
    235 ab4

    I want to be able to make two records out of the above data the follwing way.
    Key Field1 field2 field3
    234 ab1 ab2 ab5
    235 ab1 ab2 ab4

    Can any one help me with the SP to achieve this.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    do you have any criteria for putting values in field1,field2,field3 or is it random ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: SQL Problem

    Originally posted by rvv
    Hi All

    I have this problem !!!!!

    I want to create one record for multiple records in SQL 7.0. Example.

    I have multiple rows in a table with say following data

    Key Field1
    234 ab1
    234 ab2
    234 ab5
    235 ab1
    235 ab2
    235 ab4

    I want to be able to make two records out of the above data the follwing way.
    Key Field1 field2 field3
    234 ab1 ab2 ab5
    235 ab1 ab2 ab4

    Can any one help me with the SP to achieve this.

    Thanks
    check BOL under PIVOT TABLES

  4. #4
    Join Date
    Mar 2004
    Posts
    14

    Re: SQL Problem

    Originally posted by rvv
    Hi All

    I have this problem !!!!!

    I want to create one record for multiple records in SQL 7.0. Example.

    I have multiple rows in a table with say following data

    Key Field1
    234 ab1
    234 ab2
    234 ab5
    235 ab1
    235 ab2
    235 ab4

    I want to be able to make two records out of the above data the follwing way.
    Key Field1 field2 field3
    234 ab1 ab2 ab5
    235 ab1 ab2 ab4

    Can any one help me with the SP to achieve this.

    Thanks
    U CAN TRY WITH A CURSOR
    FOR EXAMPLE

    Declare @Id int,
    @descr char(5),
    @descr2 char(5)
    Declare Id_cursor cursor for
    Select distinct id from demo
    open id_cursor
    Fetch next from id_cursor
    into @id
    while @@fetch_status = 0
    begin
    Declare descr_cursor cursor for
    select descr from demo where id = @id
    Open descr_cursor
    Fetch next from descr_cursor
    into @descr
    Set @descr2 = ''
    While @@fetch_status = 0
    begin
    if @descr2 = ''
    Set @descr2 = @descr
    else
    Set @descr2 = @descr2 + @descr

    Fetch next from descr_cursor
    into @descr
    end
    print str(@id)+' '+ @descr2
    close descr_cursor
    deallocate descr_cursor
    Fetch next from id_cursor
    into @id
    end
    close id_cursor
    deallocate id_cursor

    I HOPE THAT IT HELPS U

    - CRISTOPHER SERRATO

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check out Scotts solution...and check my feable attempt

    http://www.experts-exchange.com/Data..._20900384.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    nope Brett this is the classic "List of" problem
    simple derivate of "Tree of" (remember the children/dad/granddad problem


    just a few minutes for the solution...

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Create this function :

    CREATE Function ListOfFields(@Key Varchar(3))
    RETURNS Varchar(2000)
    As
    Begin
    Declare @List as VarChar(2000)

    set @List=''
    Select @List = @List + ' ' + RTrim(Field) From Tbl Where RTrim(Key)=RTrim(@Key)

    Return(LTrim(@List))
    End

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Karolyn .. you got me worried .. which children / Dad / Granddad are you talking about ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    then you can do

    Select Key, dbo.ListOfFields(Key)
    From Tbl



    you can put a Distinct

    Select Distinct Key, dbo.ListOfFields(Key)
    From Tb

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    see the "Complex Delet Query .. Help me!" post
    from last week

    that was a "TreeView" pb

  11. #11
    Join Date
    Mar 2004
    Location
    India
    Posts
    2

    SQL Problem

    Hi All

    Thanks for all who gave suggestions for my problem, but unfortunately nothing works. Can any one suggest a better way

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select Key,
    max(Case Ordinal when 1 then Field1 end) as Field1,
    max(Case Ordinal when 2 then Field1 end) as Field2,
    max(Case Ordinal when 3 then Field1 end) as Field3
    from
    (select Key, Field1, count(*) as Ordinal
    from YourTable
    inner join YourTable YourTable2
    on YourTable.Key = YourTable2.Key
    and YourTable.Field1 >= YourTable2.Field1
    having count(*) <= 3) Subquery
    group by Key

    I hope you have a good reason for doing this....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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