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
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?
Member
8 Points
22 Posts
get data from two sql server table
Dec 17, 2020 02:44 PM|silbahi|LINK
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
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
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 :
<div> <div> </div> <div>CREATE PROCEDURE MyPROC</div> <div></div> <div>AS</div> <div>BEGIN</div> <div> </div> <div> DECLARE @Nom Varchar(50)</div> <div> DECLARE @Valeur Varchar(100)</div> <div> </div> <div> DECLARE @QUERY as NVARCHAR(4000)</div> <div> DECLARE @QUERY2 as NVARCHAR(4000)</div> <div> set @QUERY2=''</div> <div> </div> <div> SET @QUERY = 'SELECT NAME_POSTE '</div> <div> </div> <div> </div> <div> DECLARE CURSOR_Params CURSOR FORWARD_ONLY DYNAMIC </div> <div> FOR SELECT [NAME_PARAM],[VALUE_PARAM]</div> <div></div> <div> FROM [PARAMS_LIST_POSTES]</div> <div> </div> <div> OPEN CURSOR_Params</div> <div> </div> <div> FETCH NEXT FROM CURSOR_Params </div> <div> INTO @Nom,@Valeur</div> <div></div> <div> </div> <div> WHILE @@FETCH_STATUS = 0</div> <div> BEGIN</div> <div></div> <div> BEGIN</div> <div> SET @QUERY2=@QUERY2+','''+@Valeur+''' As '''+@Nom+''' '</div> <div> END</div> <div></div> <div> </div> <div> FETCH NEXT FROM CURSOR_Params </div> <div> INTO @Nom,@Valeur</div> <div> </div> <div> END</div> <div> CLOSE CURSOR_Params</div> <div> DEALLOCATE CURSOR_Params</div> <div> </div> <div> </div> <div> EXEC ( @QUERY + @Query2 + ' FROM [LIST_POSTES ] </div> <div> INNER JOIN [PARAMS_LIST_POSTES] ON [LIST_POSTES ].PK_POSTE = [PARAMS_LIST_POSTES].FK_POSTE')</div> <div> </div> <div>END</div> <div> </div> <div></div> <div>I get this as result </div> <div></div> <div>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</div> <div></div> <div>But I want to get this </div> <div></div> <div></div> <div>NAME_POSTE NamePrinter PathPrinter </div> <div>
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
Poste1 name imp 1 path 1
Poste2 name imp 2 path 2
Any help please !!
</div> </div>All-Star
57864 Points
15491 Posts
Re: get data from two sql server table
Dec 17, 2020 04:02 PM|bruce (sqlwork.com)|LINK
simple pivot query:
if sqlserver you can use the pivot clause
Member
8 Points
22 Posts
Re: get data from two sql server table
Dec 18, 2020 07:56 AM|silbahi|LINK
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?