SQL query to report on multiple values in a column

I have an MSSQL DB that contains all of the details I need to create a report but I'm trying to format the output in a specific way. Below is a small sample of columns and values in the "MachineProperties" table. Ideally, what I'm trying for is an SQL query that will create a report that provides a row for each machine with all of the property names and property values like this:

id, servername, zone, owner,

columns from the MachineProperties table
ID                    PropertyName      PropertyValue
id001            zone                    dmz
id001            owner                    wc
id001            servername              server1
id002            owner                    jb
id002            zone                    dmz
id002            servername              server2
id003            zone                    prod
id003            owner                    jb
id003            servername              server3
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
You need to pivot the table ,something like this:

select * from MachineProperties
pivot ( max(PropertyValue) for PropertyName in ([zone],[Owner],[servername]))as ResultSet
LN41Author Commented:
Hi. That returns the table but I need rows formatted as id, servername, zone, owner.
Mark WillsTopic AdvisorCommented:
Is this for a comma delimited export ?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Ares KurkluSoftware EngineerCommented:
You can specify the order in the select statement:

select  id, servername, zone, owner from MachineProperties 
pivot ( max(PropertyValue) for PropertyName in ([zone],[Owner],[servername]))as ResultSet

Open in new window

if you want comma you can simply do  id +','+ servername+','+  zone+','+  owner

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Agree, pivot is the quick way
select  right(id,3)+','+ servername+','+ zone+','+ owner as [output]
from MachineProperties 
pivot ( max(PropertyValue) for PropertyName in ([servername],[zone],[Owner])) P

Open in new window

Mark WillsTopic AdvisorCommented:
if you are PIVOT adverse,
select right(m.id,3)+','+isnull(s.propertyvalue,'')+','+isnull(z.propertyvalue,'')+','+isnull(o.propertyvalue,'') as [output]
from (select distinct id from MachineProperties) m
left outer join MachineProperties s on m.id = s.id and s.PropertyName = 'servername'
left outer join MachineProperties z on m.id = z.id and z.PropertyName = 'zone'
left outer join MachineProperties o on m.id = o.id and o.PropertyName = 'owner'

Open in new window

Now, the reason for left outer join is to handle the situation where a property doesnt exist, and (as we should also do in the PIVOT) when stringing together columns that might be NULL, we need to manage that with isnull() otherwise the whole [output] string will become NULL
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.