SQL query to get multiple records into multiple columns

February 9, 2022

I have a CSV that I am trying to create and pull data for. The CSV template has multiple columns for Status (IE: Status1, Status2, Status3). Some locations have only one status and some can have up to 3. I am trying to find a way in SQL that I can have 3 status columns and populate those based on the number of records for a location. For example, a location called John’s Office could have a status of Ready. Another location called IT Workroom may have 3 statuses, Ready, In-Repair, and In-Use. The columns in the SQL query would look something like this:

Location          Status1         Status2        Status3
----------------------------------------------------------
John's Office     Ready
IT Workroom       Ready           In-Repair       In-Use

The column names Status1, Status2, etc would be column names that are just made up. I would want to populate Status1 with the first record that returns, regardless of the actual status name. So Status1 would always be populated. Status2 will only be populated if their is a second status.

In the original table that holds this information, it looks like this:

LocationName           StatusName
------------------------------------
IT Workroom            In-Repair
IT Workroom            Ready
John's Office          Ready
IT Workroom            In-Use

I have tried PIVOT, but I realized that this is not what I need as I am not technically aggregating the data.

>Solution :

you can use row number with max case when like this:

select LocationName, MAX(CASE WHEN RowNum = 1 THEN StatusName ELSE NULL END) Status1
    , MAX(CASE WHEN RowNum = 2 THEN StatusName ELSE NULL END) Status2
    , MAX(CASE WHEN RowNum = 3 THEN StatusName ELSE NULL END) Status3 
from (
    select *, ROW_NUMBER() OVER (PARTITION BY LocationName ORDER BY LocationName, StatusName) RowNum
    from #data
) Recs
group by LocationName