Inventory number (Environment Variable) doesn't show

Answered

Comments

4 comments

  • Christian Bacher

    Hi Martin,

    have you tried a simple report instead of a SQL report to get your result?

    It's a very simple report, to simple for complex SQL strings 😉

    ~Chris

    0
    Comment actions Permalink
  • Martin Johne

    Thanks 😃 This works, but that was the first step. My problem is we have 3 Environment Variables. Current we get the result rows like:

    Computername 1  -   Environment Variables 1
    Computername 1  -   Environment Variables 2
    Computername 1  -   Environment Variables 3
    Computername 2  -   Environment Variables 1
    Computername 2  -   Environment Variables 2
    Computername 2  -   Environment Variables 3
    

    but i need a tabel form

    Computername 1 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3
    Computername 2 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3
    Computername 3 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3
    

    my idea was a inner join.

    Or is there a trick for this problem.

    Kind Regards Martin

    0
    Comment actions Permalink
  • Colby Bouma

    I think you'll have to do a pivot like this:

    SELECT
          Computers.Name AS "Computer Name"
        , Computers.Description
        -- https://stackoverflow.com/a/3611606
        , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer1' THEN EnvironmentVariables.Value END) AS "Inventarnummer 1"
        , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer2' THEN EnvironmentVariables.Value END) AS "Inventarnummer 2"
        , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer3' THEN EnvironmentVariables.Value END) AS "Inventarnummer 3"
    FROM
        Computers
    INNER JOIN
        EnvironmentVariables USING (ComputerId)
    WHERE
        <ComputerFilter>
    GROUP BY Computers.Name
    
    0
    Comment actions Permalink
  • Martin Johne

    Jeah. Many Thanks. Looks awesome.

    0
    Comment actions Permalink

Please sign in to leave a comment.