SQL query to get multiple records into multiple columns-CodePudding

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.

CodePudding user response：

Please try the following solution mimicking PIVOT operation.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, LocationName VARCHAR(30), StatusName VARCHAR(50));
INSERT INTO @tbl VALUES
('IT Workroom', 'In-Repair'),
('IT Workroom', 'Ready'),
('John''s Office', 'Ready'),
('IT Workroom', 'In-Use');
-- DDL and sample data population, end

SELECT  LocationName
   , MAX(CASE WHEN StatusName = 'Ready' THEN StatusName ELSE '' END) [Status1]
   , MAX(CASE WHEN StatusName = 'In-Repair' THEN StatusName ELSE '' END) [Status2]
   , MAX(CASE WHEN StatusName = 'In-Use' THEN StatusName ELSE '' END) [Status3]
FROM @tbl
GROUP BY LocationName
ORDER BY LocationName;

Output

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

CodePudding user response：

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

CodePudding user response：

This is very simple solution considering you have just 3 different types of statuses so the code is not so much replicated.

insert into new_locations (
    select distinct location,
        case when location in (select distinct location from old_locations where status = 'ready') then 'ready' else '' end,
        case when location in (select distinct location from old_locations where status = 'active') then 'active' else '' end,
        case when location in (select distinct location from old_locations where status = 'dead') then 'dead' else '' end
    from old_locations
);

SQL is quite handy and this is just the case where you can kind of cheat with using bit of code replication, it is very simple to understand and does its job. There usually is some pure SQL solution for this kind of problems without need of using T-SQL or other sql 'upgrades'.