Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    8

    Unhappy Unanswered: View and link table

    Hello

    I'm searching some help about views and link table. I have three tables, a
    link table has three rows refering elements in the two others table. I make an example

    TABLE A
    1 Car

    Table B
    1 Father
    2 Mother
    3 Grand Father

    Table C
    1 1
    1 2
    1 3

    I want to create a view to have something like this

    View myView
    1 Father Mother GrandFather

    But I don't find a solution to do the difference between the result and I have something like this :

    view myView
    1 Father Father Father

    It stays on the first element even I do the join for each elements.

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: View and link table

    I don't know exactly what the problem was with your query because you didn't post it, but you should try this one:

    Consider the following three tables:

    Table A (IDObject bigint, Name varchar(50))
    Table B (IDUser bigint, Name varchar(50))
    Table C (IDObject bigint, IDUser bigint)

    In SQL Server 2k create a store proc with the following T-SQL code:

    declare @CrtId bigint,@str varchar(50)
    declare @T1 table(IDObject bigint,Expl varchar(300))
    declare Crs cursor for
    select distinct IDObject from C order by 1

    open Crs
    fetch next from Crs INTO @CrtId
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @str=''
    select @str=@str+isnull(x.Name,'')+',' from
    (select Name from C join B on C.IDUser=B.IDUser where C.IDObject=@CrtId) x
    insert into @T1 values(@CrtId,(case when len(@str)=0 then '' else left(@str,len(@str)-1) end))
    fetch next from Crs INTO @CrtId
    END
    close Crs
    deallocate Crs
    select Name,Expl from @T1 as T join A on T.IDObject=A.IDObject


    Good Look!

    IONUT

    PS Using cursor's may slowdown your application if a large volume of data involving.


    If you want multiple columns to be defined for various users of object (and not concatenate the user name in one column) first you must determine the number of columns required, then define a temp table with those columns and then populate the table with values.

    See here the fragment of code for determining the number of columns and for create the temp table as you need:


    declare @maxit bigint,@k bigint,@str nvarchar(200)
    set @maxit=(select max(nr) from (select count(*) as nr from C group by IDObject) S)
    create table #Temp (IDUser bigint)
    set @k=1
    while @k<=@maxit
    BEGIN
    set @str='alter table #Temp add c_'+ltrim(rtrim(str(@k)))+' varchar(50)'
    EXEC sp_executesql @str
    set @k=@k+1
    END

    <insert your code here>


    drop table #Temp

  3. #3
    Join Date
    Dec 2002
    Posts
    8

    Re: View and link table

    Hello

    you give me a good way to select the values, it works fine thank you. But when I want to take this in a view, how can I do this???

    It's very important for me to do this, please help me. If you need further informations, don't hesitate to ask me.

    Thank you

    Originally posted by ionut calin
    I don't know exactly what the problem was with your query because you didn't post it, but you should try this one:

    Consider the following three tables:

    Table A (IDObject bigint, Name varchar(50))
    Table B (IDUser bigint, Name varchar(50))
    Table C (IDObject bigint, IDUser bigint)

    In SQL Server 2k create a store proc with the following T-SQL code:

    declare @CrtId bigint,@str varchar(50)
    declare @T1 table(IDObject bigint,Expl varchar(300))
    declare Crs cursor for
    select distinct IDObject from C order by 1

    open Crs
    fetch next from Crs INTO @CrtId
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @str=''
    select @str=@str+isnull(x.Name,'')+',' from
    (select Name from C join B on C.IDUser=B.IDUser where C.IDObject=@CrtId) x
    insert into @T1 values(@CrtId,(case when len(@str)=0 then '' else left(@str,len(@str)-1) end))
    fetch next from Crs INTO @CrtId
    END
    close Crs
    deallocate Crs
    select Name,Expl from @T1 as T join A on T.IDObject=A.IDObject


    Good Look!

    IONUT

    PS Using cursor's may slowdown your application if a large volume of data involving.


    If you want multiple columns to be defined for various users of object (and not concatenate the user name in one column) first you must determine the number of columns required, then define a temp table with those columns and then populate the table with values.

    See here the fragment of code for determining the number of columns and for create the temp table as you need:


    declare @maxit bigint,@k bigint,@str nvarchar(200)
    set @maxit=(select max(nr) from (select count(*) as nr from C group by IDObject) S)
    create table #Temp (IDUser bigint)
    set @k=1
    while @k<=@maxit
    BEGIN
    set @str='alter table #Temp add c_'+ltrim(rtrim(str(@k)))+' varchar(50)'
    EXEC sp_executesql @str
    set @k=@k+1
    END

    <insert your code here>


    drop table #Temp

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: View and link table

    Make it a function not a storeproc. Functions can return a table variable and so, they can be used like any other table in an SQL statement.


    ionut calin

    PS

    See Books on Line (BOL) for more information about user defined functions

Posting Permissions

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