locked
get data from two sql server table RRS feed

  • Question

  • User202943253 posted

    Hello,

    I have two tables in my DB sql Server : 

    Table1: LIST_POSTES  columns : PK_POSTE , NAME_POSTE

    PK_POSTE  NAME_POSTE
    ----------- --------
    1           Poste1     
    2           Poste2    

    Table 2: PARAMS_LIST_POSTES columns: FK_POSTE,NAME_PARAM, VALUE_PARAM

    FK_POSTE NAME_PARAM                                         VALUE_PARAM
    ----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
    1           PathPrinter                                           path 1
    1           NamePrinter                                        name imp 1
    2          PathPrinter                                           path 2
    2          NamePrinter                                         name imp 2

     when calling a strored procedure, I want to display in my gridView a data like this: 

    NAME_POSTE       NamePrinter                                  PathPrinter                                           
    ----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------

    Poste1                name imp 1                                     path 1

    Poste2               name imp 2                                      path 2

    My stored procedure : 

     
    CREATE PROCEDURE MyPROC
    AS
    BEGIN
    DECLARE @Nom Varchar(50)
    DECLARE @Valeur Varchar(100)
     
    DECLARE @QUERY as NVARCHAR(4000)
    DECLARE @QUERY2 as NVARCHAR(4000)
    set @QUERY2=''
     SET @QUERY = 'SELECT NAME_POSTE  '
     
     
    DECLARE CURSOR_Params CURSOR FORWARD_ONLY DYNAMIC 
    FOR SELECT [NAME_PARAM],[VALUE_PARAM]
    FROM [PARAMS_LIST_POSTES]
     
    OPEN CURSOR_Params
     
    FETCH NEXT FROM CURSOR_Params 
    INTO @Nom,@Valeur
     
    WHILE @@FETCH_STATUS = 0
    BEGIN
    BEGIN
    SET @QUERY2=@QUERY2+','''+@Valeur+''' As '''+@Nom+''' '
    END
    FETCH NEXT FROM CURSOR_Params 
    INTO @Nom,@Valeur
     
    END
    CLOSE CURSOR_Params
    DEALLOCATE CURSOR_Params
    EXEC ( @QUERY + @Query2 + ' FROM [LIST_POSTES ]  
    INNER JOIN [PARAMS_LIST_POSTES] ON [LIST_POSTES ].PK_POSTE = [PARAMS_LIST_POSTES].FK_POSTE')
     
    END
     
    I get this as result 
    NAME_POSTE      PathPrinter      NamePrinter   PathPrinter      NamePrinter
    --------        ---------------- ------------- ---------------- -------------
    Poste1             path 1           name imp 1    path 2           name imp 2
    Poste1             path 1           name imp 1    path 2           name imp 2
    Poste2             path 1           name imp 1    path 2           name imp 2
    Poste2             path 1           name imp 1    path 2           name imp 2
    But I want to get this 
    NAME_POSTE       NamePrinter                                  PathPrinter                                           

    ----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------

    Poste1                name imp 1                                     path 1

    Poste2               name imp 2                                      path 2

    Any help please !!

    Thursday, December 17, 2020 2:44 PM

Answers

  • User-474980206 posted

    simple pivot query:

    select PK_POSTE, 
    	max(case when NAME_PARAM = 'PathPrinter' then VALUE_PARAM else '' end) as NamePrinter,
    	max(case when NAME_PARAM = 'NamePrinter' then VALUE_PARAM else '' end) as PathPrinter
    from LIST_POSTES 
    join PARAMS_LIST_POSTES on FK_POSTE = PK_POSTE
    group by PK_POSTE

    if sqlserver you can use the pivot clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2020 4:02 PM

All replies

  • User-474980206 posted

    simple pivot query:

    select PK_POSTE, 
    	max(case when NAME_PARAM = 'PathPrinter' then VALUE_PARAM else '' end) as NamePrinter,
    	max(case when NAME_PARAM = 'NamePrinter' then VALUE_PARAM else '' end) as PathPrinter
    from LIST_POSTES 
    join PARAMS_LIST_POSTES on FK_POSTE = PK_POSTE
    group by PK_POSTE

    if sqlserver you can use the pivot clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2020 4:02 PM
  • User202943253 posted

    Thanks you !

    and if I would like to make a call to a stored procedure that allows me to do an insertion or an update on the data of the generated table and behind do the insertions or updates on the other two tables.
    are we doing the same concept?
    what can you suggest to me?

    Friday, December 18, 2020 7:56 AM