Take the 2-minute tour ×

I am doing a report on the desktops in my company and the software programs that are currently installed on it.

For example, let's say we have three columns that are guaranteed to return only one row per machine: Location, Last_logged-on_User, Machine_Name and one that will return one or more rows: Software. I want the first row to display all the single-row columns as well as the first row of the Software column and the rest of rows displaying only the Software rows and fill the single-row columns with blanks.

Location Last_logged-on_User Machine_Name Software_name
Venus    James.Jiao          McKain       Internet Explorer
                                          Antivirus X
                                          Office Suite
Mars     Veronica.Mars       Obama        Internet Explorer
                                          Google Chrome
                                          Adobe Acrobat
Jupiter  Taylor.Swift        Bachmann     Opera
                                          Speech Helper
                                          Dictionary for Kids

I am looking for the query for this, but I am not quite sure how to start with this as I can't think of way to conditionally blank the single-row columns in some rows.

Here is the table structure if you need it

Create table Machine
  machine_id int not null,
  machine_name varchar(25) not null,
  last_logged-on_user varchar(25) not null,
  location varchar(25) not null

Create table SoftwareList
  machine_id int not null,
  software_name varchar(25) not null
share|improve this question
I have included the table structure –  deutschZuid Apr 23 '12 at 22:03

1 Answer 1

up vote 4 down vote accepted

You could use Row_Number() Over (Partition by to establish whether it is the first occurrence in a subquery, and then use a case statement to only return location when row number is 1.

    CASE WHEN RowNo = 1 OR RowNo IS NULL THEN location ELSE '' END as Location,

        sl.Software_name ,
        ROW_NUMBER() OVER(Partition by location ORDER by location) as rowNo
  FROM Machine m
  LEFT JOIN Softwarelist sl
  ON sl.Machine_id = m.machine_id

  ) d

See SQL Fiddle here

Hope that helps

share|improve this answer
This works like a charm. I suspected row_number() would be involved somehow, but didn't have the mental acuity to get beyond that! –  deutschZuid Apr 23 '12 at 23:00

Your Answer


