December 17, 2020 at 2:59 pm
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 :
-------- ---------------- ------------- ---------------- -------------
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 !!
December 17, 2020 at 4:49 pm
First create some consumable test data which you should have provided:
CREATE TABLE #L
(
PK_POSTE int NOT NULL PRIMARY KEY
,NAME_POSTE varchar(20) NOT NULL
);
INSERT INTO #L
VALUES (1, 'Poste1'), (2, 'Poste2');
CREATE TABLE #P
(
FK_POSTE int NOT NULL
,NAME_PARAM varchar(20) NOT NULL
,VALUE_PARAM varchar(20) NOT NULL
,PRIMARY KEY (FK_POSTE, NAME_PARAM)
);
INSERT INTO #P
VALUES (1, 'PathPrinter','path 1')
,(1, 'NamePrinter','name imp 1')
,(2, 'PathPrinter','path 2')
,(2, 'NamePrinter','name imp 2');
Keep the code set based as this is what SQL is good at:
SELECT NAME_POSTE, NamePrinter, PathPrinter
FROM
(
SELECT L.NAME_POSTE, P.NAME_PARAM, P.VALUE_PARAM
FROM #P P
JOIN #L L
ON P.FK_POSTE = L.PK_POSTE
) S
PIVOT
(
MAX(VALUE_PARAM)
FOR NAME_PARAM IN (NamePrinter, PathPrinter)
) P;
December 18, 2020 at 1:42 am
Using conditional aggregation
select NAME_POSTE,
max(iif(NAME_PARAM='NamePrinter', VALUE_PARAM, null)) NamePrinter,
max(iif(NAME_PARAM='PathPrinter', VALUE_PARAM, null)) PathPrinter
from #P p
join #L l ON p.FK_POSTE = l.PK_POSTE
group by NAME_POSTE;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 18, 2020 at 7:57 am
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?
December 18, 2020 at 10:54 am
I have a form with different fields that I should to save or to edit the data into two different tables.
The form contains for example:
NAME_POSTE: poste1
PathPrinter: path1
NamePrinter: name imp 1
when adding a new data or when editing an element, I want to save it into two table:
Poste
PK_POSTE ------NAME_POSTE
1 ------------------poste1
2 -----------------poste2
Params_Poste
FK_POSTE ------- NAME_PARAM ---------- VALUE_PARAM
1---------------------PathPrinter------------------path 1
1---------------------NamePrinter----------------name imp 1
2---------------------PathPrinter-----------------path 2
2---------------------NamePrinter----------------name imp 2
any help please ?
Thanks!
December 18, 2020 at 2:32 pm
Please post the CREATE TABLE Data Definition Language (DDL) of both of your tables. Without knowing how the keys (PK_POSTE , FK_POSTE) and constraints are defined we don't have enough information.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 18, 2020 at 4:34 pm
You can do merge to #L in the proc and output the insert/deleted values to a temp table, then use the temp table to merge values to #P table
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2020 at 6:26 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply