CODEWITHSUNDEEP
sqlsql-server
Elwin Hidding
Elwin Hidding

Reputation: 11

MSSQL query to combine information in a new table

My table: 

servername: | FactName: | FactValue:
server01    | CPUcores  | 2
server01    | Memory    | 64GB
server01    | HDD01size | 50GB
server01    | HDD02size | 60GB
server01    | HDD03size | 70GB
server02    | CPUcores  | 4
server02    | Memory    | 32GB
server02    | HDD01size | 50GB
server02    | HDD02size | 60GB
server02    | OS        | Windows 2012 R2

What i try to create with a MSSQL query is a new table with the following output:

servername:|CPUcores:|Memory:|HDD01size:|HDD02size:|HDD03size:|OS:   
server01   |2        |64GB   |50GB      |60GB      |70GB      | -     
server02   |4        |32GB   |50GB      |60GB      | -        |Windows 2012 R2

The columns need to be dynamically created during the fetching of the data. The column names are:

SELECT DISTINCT [FactName]
FROM [table]

...with the addition of the column "servername".

I can't figure out what the best way is to accomplish this.

Upvotes: 0

Views: 22

Answers (1)

sgeddes
sgeddes

Reputation: 62841

This is a referred to as table pivoting. Here's one option using conditional aggregation:

select servername, 
    max(case when FactName = 'CPUcores' then FactValue end) as Cpucores,
    max(case when FactName = 'Memory' then FactValue end) as Memory,
    max(case when FactName = 'HDD01size' then FactValue end) as HDD01size,
    max(case when FactName = 'HDD02size' then FactValue end) as HDD02size,
    max(case when FactName = 'HDD03size' then FactValue end) as HDD03size,
    max(case when FactName = 'OS' then FactValue end) as OS
from yourtable 
group by servername

If you don't know all the potential values/columns, then you'll need to use dynamic sql.

Upvotes: 1

Related Questions