sql - How can I put multiple values from one column spread out into multiple columns? - Stack Overflow

admin管理员组

文章数量:1122832

The table looks like this now;

CompanyLocationName	LineName
CompanyA	Auto
CompanyA	Home
CompanyA	Life
CompanyB	Auto

The table looks like this now;

CompanyLocationName	LineName
CompanyA	Auto
CompanyA	Home
CompanyA	Life
CompanyB	Auto

Is there a way to alter the query to make the table look like this?

CompanyLocationName	LineName	LineName2	LineName3
CompanyA	Auto	Home	Life
CompanyB	Auto	null	null

Basically if a CompanyLocationName has multiple LineNames, i would like them spread out into multiple columns like LineName2, LineName3, LineName4, etc. If a CompanyLocationName does not have extra LineNames to fill those extra columns, it will just show up as null

• sql
• sql-server

Share Improve this question Follow edited Nov 21, 2024 at 15:43 Mark Rotteveel 109k224224 gold badges155155 silver badges218218 bronze badges asked Nov 21, 2024 at 14:42 Quinn JohnsonQuinn Johnson 322 bronze badges 6

• 2 Please replace images with formatted text and also tag your DBMS. – Isolated Commented Nov 21, 2024 at 14:44
• I.e. provide a complete minimal reproducible example. – jarlh Commented Nov 21, 2024 at 14:44
• Please tag the actual DBMS you are using. Some have PIVOT operators and others don't. – Chris Maurer Commented Nov 21, 2024 at 14:57
• @Isolated I have fixed it now thank you – Quinn Johnson Commented Nov 21, 2024 at 15:33
• Your DBMS is not ssms-19, that is just the tool you use to connect; you're using SQL Server (sql-server), though you may also want to add a tag for the version you're using. – Mark Rotteveel Commented Nov 21, 2024 at 15:43

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

You need to pivot the rows into columns. In SQL Server, you can do this using a max case method or by using the PIVOT operator (depending on your version).

In either case, you will need a key to enforce the order of the lines. In the below example I am using row_number() to create this based on the table order, but you likely have this column in your table already.

declare @YourTable table (RowId int identity(1,1) primary key, CompanyLocationName varchar(100), LineName varchar(100));
insert into @YourTable
    values
    ('CompanyA', 'Auto'),
    ('CompanyA', 'Home'),
    ('CompanyA', 'Life'),
    ('CompanyB', 'Auto')



-- using a "max case" to pivot the rows to columns
select  CompanyLocationName, 
        max(iif(r=1, LineName, null)) as LineName, 
        max(iif(r=2, LineName, null)) as LineName2, 
        max(iif(r=3, LineName, null)) as LineName3
from (  select CompanyLocationName, LineName, [r]= row_number()over(partition by CompanyLocationName order by RowId)
        from @YourTable
) as d
group
by CompanyLocationName;



-- using the PIVOT operator
select  CompanyLocationName, 
        [1] as LineName,
        [2] as LineName2,
        [3] as LineName3
from (  select CompanyLocationName, LineName, [r]= row_number()over(partition by CompanyLocationName order by RowId)
        from @YourTable
) as d
pivot(max(LineName) for r in ([1], [2], [3])) as p;

Returns:

CompanyLocationName LineName    LineName2   LineName3
CompanyA            Auto        Home        Life
CompanyB            Auto        NULL        NULL

本文标签： sqlHow can I put multiple values from one column spread out into multiple columnsStack Overflow