Post reply

need help with writing query to retrieve info stored in multiple columns

  • March 26, 2019 at 4:38 am

    #2031663

    I need help with creating query to retrieve info stored in multiple columns.  Below you can find sample table/info with current output and the required/preferred output.
    Table
    create table dbo.netwdriverinfo (
    machid [int] not null,
    driver0 [nvarchar] (100) null,
    driverversion0 [nvarchar] (100) null,
    driver1 [nvarchar] (100) null,
    driverversion1 [nvarchar] (100) null,
    driver2 [nvarchar] (100) null,
    driverversion2 [nvarchar] (100) null,
    driver3 [nvarchar] (100) null,
    driverversion3 [nvarchar] (100) null,
    ) ON [PRIMARY]

    --Add Dummy data
    insert into dbo.netwdriverinfo (machid, driver0, driverversion0, driver1, driverversion1, driver2, driverversion2, driver3, driverversion3)
    VALUES (1, 'Intel LAN', '1.2.2.0', 'Intel WLAN', '1.2.2.0', 'Microsoft Internal', '1.4.4.4', 'Microsoft Internal', '1.4.4.4')

    insert into dbo.netwdriverinfo (machid, driver0, driverversion0, driver1, driverversion1, driver2, driverversion2, driver3, driverversion3)
    VALUES (2, 'Intel WLAN', '1.2.2.0', 'Intel LAN', '1.2.2.0', 'Microsoft Internal', '1.4.4.4', 'Microsoft Internal', '1.4.4.4')

    insert into dbo.netwdriverinfo (machid, driver0, driverversion0, driver1, driverversion1, driver2, driverversion2, driver3, driverversion3)
    VALUES (3, 'Intel LAN', '1.2.2.0', 'Microsoft Internal', '1.4.4.4', 'Intel WLAN', '1.2.2.0', 'Microsoft Internal', '1.4.4.4')

    insert into dbo.netwdriverinfo (machid, driver0, driverversion0, driver1, driverversion1, driver2, driverversion2, driver3, driverversion3)
    VALUES (4, 'Microsoft Internal', '1.4.4.4', 'Intel WLAN', '1.2.4.0', 'Microsoft Internal', '1.4.4.4', 'Microsoft Internal', '1.4.4.4')

    insert into dbo.netwdriverinfo (machid, driver0, driverversion0, driver1, driverversion1, driver2, driverversion2, driver3, driverversion3)
    VALUES (5, 'Intel LAN', '1.2.2.0', 'Microsoft Internal', '1.4.4.4', 'Microsoft Internal', '1.4.4.4', 'Intel WLAN', '1.2.2.0')

    select * from dbo.netwdriverinfo
    --Output from the query

    machiddriver0driverversion0driver1driverversion1driver2driverversion2driver3driverversion3
    1Intel LAN1.2.2.0Intel WLAN1.2.2.0Microsoft Internal1.4.4.4Microsoft Internal1.4.4.4
    2Intel WLAN1.2.2.0Intel LAN1.2.2.0Microsoft Internal1.4.4.4Microsoft Internal1.4.4.4
    3Intel LAN1.2.2.0Microsoft Internal1.4.4.4Intel WLAN1.2.2.0Microsoft Internal1.4.4.4
    4Microsoft Internal1.4.4.4Intel WLAN1.2.4.0Microsoft Internal1.4.4.4Microsoft Internal1.4.4.4
    5Intel LAN1.2.2.0Microsoft Internal1.4.4.4Microsoft Internal1.4.4.4Intel WLAN1.2.2.0

    I would like to get info from the machines that have "Intel WLAN" driver

    Desired output would like like below
    machid                 WLANDriver             WLANdriverversion
    1                           Intel WLAN                    1.2.2.0
    2                           Intel WLAN                    1.2.2.0
    3                           Intel WLAN                    1.2.2.0
    4                           Intel WLAN                    1.2.4.0
    5                           Intel WLAN                    1.2.2.0

    Thx for all the help.

  • March 26, 2019 at 7:03 am

    #2031761

    first, if you can change the structure of your table, do so. you should have multiple entries for each machineNo, with only one set of driver and driverVersion columns. Then your query would be as simple as adding "WHERE driver = 'Intel WLAN'" to your select.

    As it is, you could do this with a CASE statement or by unioning queries together.
    SELECT
    [driver] = CASE
    WHEN driver0 = 'Intel WLAN' THEN driver0
    WHEN driver1 = 'Intel WLAN' THEN driver1
    WHEN driver2 = 'Intel WLAN' THEN driver2
    WHEN driver3 = 'Intel WLAN' THEN driver3
    ELSE NULL
    END,
    [driverVersion] = CASE
    WHEN driver0 = 'Intel WLAN' THEN driverVersion0
    WHEN driver1 = 'Intel WLAN' THEN driverVersion1
    WHEN driver2 = 'Intel WLAN' THEN driverVersion2
    WHEN driver3 = 'Intel WLAN' THEN driverVersion3
    ELSE NULL
    END
    FROM dbo.netwdriverinfo;

    or, alternately

    SELECT driver0, driverversion0 FROM dbo.netwdriverinfo WHERE driver0 = 'Intel WLAN'
    UNION
    SELECT driver1, driverversion1 FROM dbo.netwdriverinfo WHERE driver1 = 'Intel WLAN'
    UNION
    SELECT driver2, driverversion2 FROM dbo.netwdriverinfo WHERE driver2 = 'Intel WLAN'
    UNION
    SELECT driver3, driverversion3 FROM dbo.netwdriverinfo WHERE driver3 = 'Intel WLAN'

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • March 26, 2019 at 10:18 am

    #2031782

    thx for the quick help on this, the UNION query does solve my problem, should have thought of this myself.

  • March 26, 2019 at 10:48 am

    #2031788

    jonathan.crawford - Tuesday, March 26, 2019 7:03 AM

    first, if you can change the structure of your table, do so. you should have multiple entries for each machineNo, with only one set of driver and driverVersion columns. Then your query would be as simple as adding "WHERE driver = 'Intel WLAN'" to your select.

    As it is, you could do this with a CASE statement or by unioning queries together.
    SELECT
    [driver] = CASE
    WHEN driver0 = 'Intel WLAN' THEN driver0
    WHEN driver1 = 'Intel WLAN' THEN driver1
    WHEN driver2 = 'Intel WLAN' THEN driver2
    WHEN driver3 = 'Intel WLAN' THEN driver3
    ELSE NULL
    END,
    [driverVersion] = CASE
    WHEN driver0 = 'Intel WLAN' THEN driverVersion0
    WHEN driver1 = 'Intel WLAN' THEN driverVersion1
    WHEN driver2 = 'Intel WLAN' THEN driverVersion2
    WHEN driver3 = 'Intel WLAN' THEN driverVersion3
    ELSE NULL
    END
    FROM dbo.netwdriverinfo;

    or, alternately

    SELECT driver0, driverversion0 FROM dbo.netwdriverinfo WHERE driver0 = 'Intel WLAN'
    UNION
    SELECT driver1, driverversion1 FROM dbo.netwdriverinfo WHERE driver1 = 'Intel WLAN'
    UNION
    SELECT driver2, driverversion2 FROM dbo.netwdriverinfo WHERE driver2 = 'Intel WLAN'
    UNION
    SELECT driver3, driverversion3 FROM dbo.netwdriverinfo WHERE driver3 = 'Intel WLAN'

    I prefer the Table Value Constructor version

    SELECT ndi.*
    FROM dbo.netwdriverinfo ndi
    CROSS APPLY ( VALUES(driver0, driverversion0), (driver1, driverversion1), (driver2, driverversion2), (driver3, driverversion3) ) d(driver, driverversion)
    WHERE d.driver = 'Intel WLAN'

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • March 26, 2019 at 11:22 am

    #2031793

    Dang, well I do too now. thanks Drew!

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply