Convert multiple rows into a single row in SQL Server

Question

I want to change multiple rows into a single row based on Number and Type:

+------+---------+----------+-------+---------+-------+
| Name | Address |   City   | State | Number  | Type  |
+------+---------+----------+-------+---------+-------+
| XYZ  | 123 Rd  | New York | NY    | 123D    | Code1 |
| XYZ  | 123 Rd  | New York | NY    | 56A45   | Code2 |
| XYZ  | 123 Rd  | New York | NY    | D45256  | Code3 |
| XYZ  | 123 Rd  | New York | NY    | 345TT   | Code2 |
| XYZ  | 123 Rd  | New York | NY    | 34561NN | Code2 |
| XYZ  | 123 Rd  | New York | NY    | 84567YY | Code2 |
+------+---------+----------+-------+---------+-------+

Result

+------+---------+----------+-------+-------+-------+----------+-----------+----------+--------+
| Name | Address |   City   | State | code1 | Code2 | code2_II | Code2_III | Code2_IV | Code3  |
+------+---------+----------+-------+-------+-------+----------+-----------+----------+--------+
| XYZ  | 123 Rd  | New York | NY    | 123D  | 56A45 | 345TT    | 34561NN   | 84567YY  | D45256 |
+------+---------+----------+-------+-------+-------+----------+-----------+----------+--------+

Query I used only work for Code2 and Code2_II but I want to bring Code2_III and Code2_IV

select 
    Name, Addresss, City, State,
    min(Number) as Code1,
    (case when min(Number) <> max(Number) then max(Number) end) as Code2 
from
    t
group by 
    Name, Addresss, City, State;

Answer 1

You can use conditional aggregation:

select Name, Addresss, City, State,
       min(case when type = 'Code1' then number) as code1,
       min(case when type = 'Code2' and seqnum = 1 then number) as code2,
       min(case when type = 'Code2' and seqnum = 2 then number) as code2_II,
       min(case when type = 'Code2' and seqnum = 3 then number) as code2_III,
       min(case when type = 'Code2' and seqnum = 4 then number) as code2_IV,
       min(case when type = 'Code3' and seqnum = 1 then number) as code3
from (select t.*,
             row_number() over (partition by  Name, Addresss, City, State, type order by type) as seqnum
      from t
     ) t
group by Name, Addresss, City, State;

Note: This returns exactly six code columns. That seems to be what you expect. If you want a dynamic number of columns, you need to use dynamic SQL.

Answer 2

SELECT DISTINCT Name,
    Address,
    City,
    STATE,
    STUFF((
            SELECT ',' + [tCode1].Number
            FROM #TestTable [tCode1]
            WHERE [tCode1].Type = 'Code1'
            FOR XML PATH('')
            ), 1, 1, N'') [Code1],
    STUFF((
            SELECT ',' + [tCode2].Number
            FROM #TestTable [tCode2]
            WHERE [tCode2].Type = 'Code2'
            FOR XML PATH('')
            ), 1, 1, N'') [Code2],
    STUFF((
            SELECT ',' + [tCode3].Number
            FROM #TestTable [tCode3]
            WHERE [tCode3].Type = 'Code3'
            FOR XML PATH('')
            ), 1, 1, N'') [Code3]
FROM #TestTable;

I referenced this https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

To Rolling multiple rows into one

Result:

+------+---------+----------+-------+-------+-----------------------------+---------+
| Name | Address |   City   | State | Code1 |            Code2            |  Code3  |
+------+---------+----------+-------+-------+-----------------------------+---------+
| XYZ  | 123 Rd  | New York | NY    | 123D  | 56A45,345TT,34561NN,84567YY | D45256  |
+------+---------+----------+-------+-------+-----------------------------+---------+

Answer 3

As Tim mentions in the comments to your question, this will be very difficult to do without using dynamic SQL.

This code is not exactly what you are looking for but should get you most of the way there.

CREATE TABLE #Addresses (
    [Name] varchar(255),
    [Address] varchar(255),
    [City] varchar(255),
    [State] varchar(255),
    [Number] varchar(255),
    [Type] varchar(255)
)

INSERT INTO #Addresses
VALUES 
('XYZ','123 Rd','New York','NY','123D','Code1'),
('XYZ','123 Rd','New York','NY','56A45','Code2'),
('XYZ','123 Rd','New York','NY','D45256','Code3'),
('XYZ','123 Rd','New York','NY','345TT','Code2'),
('XYZ','123 Rd','New York','NY','34561NN','Code2'),
('XYZ','123 Rd','New York','NY','84567YY','Code2');


SELECT *
FROM (
    SELECT 
        [Name], [Address], [City], [State], [Number]
        , [Type] + '_' + LTRIM(STR(rn)) AS NewType
    FROM (
        SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY [Name], [Type] ORDER BY [Number]) rn
        FROM #Addresses
    ) a
) p
PIVOT (
    MAX([Number])
    FOR [NewType] IN ([Code1_1], [Code2_1], [Code2_2], [Code2_3], [Code2_4], [Code3_1])
) AS pvt

In short it is using ROW_NUMBER to figure out how many Numbers exist for a particular Type. It then uses a select statement to build a new name field called NewType.

From there it uses a PIVOT statement to break out the data into separate columns.

A few things to note.

1. As mentioned it is somewhat dynamic but you will have to use Dynamic SQL for the PIVOT part if you are going to run this in a larger query or have varying amount of values in the Type and Number fields. However if you know you will never have more than 4 of any code you can build the statement like this

SELECT *
FROM (
    SELECT 
        [Name], [Address], [City], [State], [Number]
        , [Type] + '_' + LTRIM(STR(rn)) AS NewType
    FROM (
        SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY [Name], [Type] ORDER BY [Number]) rn
        FROM #Addresses
    ) a
) p
PIVOT (
    MAX([Number])
    FOR [NewType] IN ([Code1_1], [Code1_2], [Code1_3], [Code1_4], [Code2_1], [Code2_2], [Code2_3], [Code2_4], [Code3_1], [Code3_2], [Code3_3], [Code3_4])
) AS pvt

Doing it this way will produce null fields for any code that doesn't have a value.

2. This will likely be slow with larger datasets.

Hope this gets you point in the right direction.