SQL Make a New Colum instead of new Row-CodePudding

I'm new to the SQL Server subject and I'm not sure how to solve my problem. I have 2 tables that I like to join based on two values of each table.

SELECT 
    dbo.ARTIKEL.ARTIKEL, 
    dbo.ARTIKEL.SUCHWORT, 
    dbo.ARTIKEL.ARTTEXT, 
    dbo.FDDATEN.INHALT AS Herstellername,
    dbo.FDDATEN.INHALT AS Typennummer,
    dbo.FDDATEN.INHALT AS Bestellnummer,
    dbo.FDDATEN.INHALT AS HerstArt,
    dbo.FDDATEN.SCHLUESSEL, 
    dbo.ARTIKEL.ROWART
FROM 
    dbo.ARTIKEL
INNER JOIN 
    FDDATEN ON ARTIKEL.ROWART = FDDATEN.SCHLUESSEL 
WHERE 
    ARTIKEL LIKE 'E%'

This works and returns something like this:

ARTIKEL	SUCHWORT	ARTTEXT	Herstellername	Typennummer	Bestellnummer	HerstArt	SCHLUESSEL	ROWART
E220324W001	C300-02400041A10100A	Commander C300, AC drive, Frame Size 2, 3PH, 400V, 4.1A, 1.5kW %	NCT.C300-02400041A10100AB100	NCT.C300-02400041A10100AB100	NCT.C300-02400041A10100AB100	NCT.C300-02400041A10100AB100	236324	236324
E220324W001	C300-02400041A10100A	Commander C300, AC drive, Frame Size 2, 3PH, 400V, 4.1A, 1.5kW %	C300-02400041A10100AB100	C300-02400041A10100AB100	C300-02400041A10100AB100	C300-02400041A10100AB100	236324	236324
E220324W001	C300-02400041A10100A	Commander C300, AC drive, Frame Size 2, 3PH, 400V, 4.1A, 1.5kW %	1	1	1	1	236324	236324
E220324W001	C300-02400041A10100A	Commander C300, AC drive, Frame Size 2, 3PH, 400V, 4.1A, 1.5kW %	Nidec	Nidec	Nidec	Nidec	236324	236324

But I want it like this way:

ARTIKEL	SUCHWORT	ARTTEXT	Herstellername	Typennummer	Bestellnummer	HerstArt	SCHLUESSEL	ROWART
E220324W001	C300-02400041A10100A	Commander C300, AC drive, Frame Size 2, 3PH, 400V, 4.1A, 1.5kW %	NCT.C300-02400041A10100AB100	C300-02400041A10100AB100	1	Nidec	236324	236324

Any ideas on how I can achieve this?

Thank you for your help.

CodePudding user response：

I assume the table FDDATEN has an additional field TYP where you store the fact weither a row is e.g a Herstellername, a Typennummer or whatever. Obviously I don't know the correct name for this column in your database nor the actual values to indicate which INHALT belongs to which TYP, but you should be able to adapt this one easily.

The Query you´re looking for is something goes somethin like this

SELECT 
        dbo.ARTIKEL.ARTIKEL, 
        dbo.ARTIKEL.SUCHWORT, 
        dbo.ARTIKEL.ARTTEXT, 
        (SELECT INHALT from dbo.FDDATEN WHERE SCHLUESSEL = ROWART AND TYP = 'Herstellername') as Herstellername,
        (SELECT INHALT from dbo.FDDATEN WHERE SCHLUESSEL = ROWART AND TYP = 'Typennummer') as Typennummer,
        (SELECT INHALT from dbo.FDDATEN WHERE SCHLUESSEL = ROWART AND TYP = 'Bestellnummer') as Bestellnummer,
        (SELECT INHALT from dbo.FDDATEN WHERE SCHLUESSEL = ROWART AND TYP = 'HerstArt') as HerstArt,
        dbo.ARTIKEL.ROWART
FROM dbo.ARTIKEL
WHERE ARTIKEL LIKE 'E%'

If you don't have a column in FDDATEN to identify the correct row for each column you probably can't get this one done. Or if there are multiple entries for one SCHLUESSEL and one TYP, you´ll get SQL errrors.

CodePudding user response：

So for later me and others my solution was this:

SELECT 
    dbo.ARTIKEL.ARTIKEL, 
    dbo.ARTIKEL.SUCHWORT, 
    dbo.ARTIKEL.ARTTEXT, 
    (SELECT INHALT from dbo.FDDATEN WHERE ROWART = SCHLUESSEL and ROWFDPOS = 1) as Herstellername,
    (SELECT INHALT from dbo.FDDATEN WHERE ROWART = SCHLUESSEL and ROWFDPOS = 11) as Typennummer,
    (SELECT INHALT from dbo.FDDATEN WHERE ROWART = SCHLUESSEL and ROWFDPOS = 21) as Bestellnummer,
    (SELECT INHALT from dbo.FDDATEN WHERE ROWART = SCHLUESSEL and ROWFDPOS = 31) as HerstArt
FROM dbo.ARTIKEL
WHERE ARTIKEL LIKE 'E%'