时间戳

首页 > 解决方案 > One column into multiple columns by type, plus concatenating multiples

sql - One column into multiple columns by type, plus concatenating multiples

问题描述

I have a table like this:

Customer Number Type
1 234.567.8910 1
1 234.234.2345 2
2 234.567.5555 1
2 151.513.5464 1
3 845.846.8486 3

I am trying to include this information with information from another table (say... address), but in separate columns by type, and I want to concatenate values that are of the same type so that the return looks like this:

Customer Cell Home Work
1 234.567.8910 234.234.2345 NULL
2 234.567.5555 & 151.513.5464 NULL NULL
3 NULL NULL 845.846.8486

When I use the STRING_AGG function, it appends the value for each line of that customer - even if it would be null when the function isn't applied, so the customer 1 has both the cell and home numbers repeated twice.

The only workaround I can find is to select each column in a subquery and join them together. Is that the only option?

标签： sqlsql-servertsql

解决方案

尝试使用FOR XML和PATH。

询问

select [Customer],
     stuff((
        select distinct ',' + [Number]
        from [your_table_name]
        where [Customer] = a.[Customer]
        and [Type] = 1
        for xml path (''))
        , 1, 1, '')  as [Cell],
    stuff((
        select distinct ',' + [Number]
        from [your_table_name]
        where [Customer] = a.[Customer]
        and [Type] = 2
        for xml path (''))
        , 1, 1, '')  as [Home],
    stuff((
        select distinct ',' + [Number]
        from [your_table_name]
        where [Customer] = a.[Customer]
        and [Type] = 3
        for xml path (''))
        , 1, 1, '')  as [Work]
from [your_table_name] as a
group by [Customer];

推荐阅读