SQL split column values into separate columns

[AlexBell]

Hello everyone, please help!
Is it possible to split the values in a column into different columns?

Выберите Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets.IPAddress,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblOIDData.Label As [OID Label],
tblO IDData.[Key] Как OID,
tblOIDData.Data As [Данные OID],
tblAssetCustom.Location,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblOIDData On tblAssets.AssetID
= tblOIDData.AssetID
Inner Join Левое внешнее соединение tblAssetCustom на tblAssets .AssetID = tblAssetCustom.AssetID
Левое внешнее соединение tsysOS On tsysOS.OScode = tblAssets.OScode
Левое соединение tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric

So that the CPU and Memory values are displayed in separate columns

[Mister_Nobody · Accepted solution]

Select Distinct Top (1000000) tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblAssets.AssetName,
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      '% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Free flash memory (bytes)') As [Free flash memory (bytes)],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'iOS version') As [iOS version],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Total flash memory (bytes)') As [Total flash memory (bytes)],
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID

[Mister_Nobody · Accepted solution]

Report 2.0

Select Top (1000000) tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblAssets.AssetName,
  eOIDData.[% Avg CPU usage (last 5 minutes)],
  eOIDData.[Free flash memory (bytes)],
  eOIDData.[iOS version],
  eOIDData.[Total flash memory (bytes)],
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join (Select tblOIDData.AssetID,
      Max(Cast(Case
        When tblOIDData.Label = '% Avg CPU usage (last 5 minutes)' Then
          tblOIDData.Data
        Else Null
      End As nvarchar)) As [% Avg CPU usage (last 5 minutes)],
      Max(Cast(Case
        When tblOIDData.Label = 'Free flash memory (bytes)' Then tblOIDData.Data
        Else Null
      End As nvarchar)) As [Free flash memory (bytes)],
      Max(Cast(Case
        When tblOIDData.Label = 'iOS version' Then tblOIDData.Data
        Else Null
      End As nvarchar)) As [iOS version],
      Max(Cast(Case
        When tblOIDData.Label = 'Total flash memory (bytes)' Then
          tblOIDData.Data
        Else Null
      End As nvarchar)) As [Total flash memory (bytes)]
    From tblOIDData
    Group By tblOIDData.AssetID) As eOIDData On tblAssets.AssetID =
      eOIDData.AssetID

[Mister_Nobody]

Fix for duplicate data - return only Maximum

Select Distinct Top (1000000) tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblAssets.AssetName,
  (Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      '% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
  (Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Free flash memory (bytes)') As [Free flash memory (bytes)],
  (Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'iOS version') As [iOS version],
  (Select Max(Cast(tblOIDData.Data As nvarchar)) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Total flash memory (bytes)') As [Total flash memory (bytes)],
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID

[Mister_Nobody · Accepted solution]

Select Distinct Top (1000000) tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblAssets.AssetName,
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      '% Avg CPU usage (last 5 minutes)') As [% Avg CPU usage (last 5 minutes)],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Free flash memory (bytes)') As [Free flash memory (bytes)],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'iOS version') As [iOS version],
  (Select Cast(tblOIDData.Data As nvarchar) From tblOIDData
    Where tblAssets.AssetID = tblOIDData.AssetID And tblOIDData.Label =
      'Total flash memory (bytes)') As [Total flash memory (bytes)],
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblOIDData On tblAssets.AssetID = tblOIDData.AssetID

[AlexBell]

thank you, very helpful!🤗

[Mister_Nobody]

Could you show what you want?

*Screenshot of excel table, for example.

[AlexBell]

source report

what kind of report is needed in the end