Convert row to columns in SQL Server

Question

I have a query that returns the results like this:

Location ID Location Code   Location Name   Division Code   sSourceCode Division Name   Business Unit Code  Business Unit Name
83806   D4009   Vanier Mitsubishi Fuso  SALES_01    SALES_01    Sales_01    SALES DEPARTMENT    Fuso Vehicle Sales Department
83806   D4009   Vanier Mitsubishi Fuso  Parts_A0    Parts_A0    Parts District A0   PARTS DEPARTMENT    Fuso Parts Department
83806   D4009   Vanier Mitsubishi Fuso  SERVICE_C1  SERVICE_C1  Service Division C1 SERVICE DEPARTMENT  Fuso Service Department

I want to have the results like this:

Location ID    Location Code    Location Name   Parts Division Code   Parts sSourceCode  Parts Division Name    Sales Division Code   Sales Division Name       Sales Business Unit Code    Sales Business Unit Name    Parts Business Unit Code    Parts Business Unit Name

So for each locationID I need only one row, with its respective parts/service/sales division/business unit Codes and Names showing up on the same row as separate columns....Not convinced I can do it with Pivoting, if possible can someone point me in the right direction or give me some examples of how this can be achieved.

Yes, you can do it with pivoting, Just google for some examples — Juan Carlos Oropeza, May 25 '16 at 03:43
Possible duplicate of [SQL Server Pivot Table with multiple column aggregates](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) — Tab Alleman, May 25 '16 at 20:03

score 0 · Answer 1 · answered May 25 '16 at 03:40

Without Pivoting you need to inner join the table to it self many times, although from the data that you posted looks like the number of rows (parts) is not static, which make you back to Pivoting again.

What i suggest you to do is to split the operation into two, where the first statement you select the rows which will be converted into columns (with an id for linking later):

ID 
Parts sSourceCode  
Parts Division Name    
Sales Division Code   
Sales Division Name       
Sales Business Unit Code    
Sales Business Unit Name

And use Pivoting on that data, then inner join this data with the same table again to get

SELECT DISTINCT Location ID Location Code   Location Name   Division Code
From Table
Inner join PreviousData

score 0 · Answer 2 · answered May 25 '16 at 04:22

I think we can use CROSS/OUTER APPLY with FOR XML to convert multiple rows into single row based on Location ID/Location Code.

Here is the final query and temp table generated to test the result.

Create Table and populate some records

 CREATE TABLE TempLocations
(
    LocationID INT,
    LocationCode VARCHAR(20),
    LocationName VARCHAR(30),
    DivisionCode VARCHAR(100),
    SourceCode VARCHAR(100),
    DivisionName VARCHAR(100),
    BusinessUnitCode VARCHAR(100),
    BusinessUnitName VARCHAR(100)

)

INSERT INTO TempLocations
VALUES
(83806, 'D4009','Vanier Mitsubishi Fuso',  'SALES_01',    'SALES_01' ,'Sales_01',  'SALES DEPARTMENT',    'Fuso Vehicle Sales Department'),
(83806,  'D4009',   'Vanier Mitsubishi Fuso',  'Parts_A0',    'Parts_A0',    'Parts District A0',   'PARTS DEPARTMENT',    'Fuso Parts Department'),
(83806,   'D4009',   'Vanier Mitsubishi Fuso',  'SERVICE_C1',  'SERVICE_C1',  'Service Division C1', 'SERVICE DEPARTMENT',  'Fuso Service Department'),
(83807, 'D4008','Borusia',  'SALES_01',    'SALES_01' ,'Sales_01',  'SALES DEPARTMENT',    'Borusia Sales Department'),
(83807,  'D4008',   'Borusia',  'Parts_A0',    'Parts_A0',    'Parts District A0',   'PARTS DEPARTMENT',    'Borusia Parts Department'),
(83807,   'D4008',   'Borusia',  'SERVICE_C1',  'SERVICE_C1',  'Service Division C1', 'SERVICE DEPARTMENT',  'Borusia Service Department')

Final Query to get data for Locations

SELECT 
 DISTINCT
    T.LocationID,
    T.LocationCode,
    T.LocationName,
    TDIV.DivisionCode,
    TS.SourceCode,
    TDIVName.DivisionName,
    TB.BusinessUnitCode,
    TBName.BusinessUnitName
FROM TempLocations AS T
CROSS APPLY
(
SELECT 
Stuff(
        (
            SELECT DISTINCT N', ' + DivisionCode 
            FROM TempLocations AS TDIV
            WHERE TDIV.LocationID=T.LocationID FOR XML PATH(''),TYPE
        )
    .value('text()[1]','nvarchar(max)'),1,2,N''
    ) AS DivisionCode
) AS TDIV
CROSS APPLY
(
SELECT 
Stuff(
        (
            SELECT DISTINCT N', ' + SourceCode 
            FROM TempLocations AS TDIV
            WHERE TDIV.LocationID=T.LocationID FOR XML PATH(''),TYPE
        )
    .value('text()[1]','nvarchar(max)'),1,2,N''
    ) AS SourceCode
) AS TS
CROSS APPLY
(
SELECT 
Stuff(
        (
            SELECT DISTINCT N', ' + DivisionName 
            FROM TempLocations AS TDIV
            WHERE TDIV.LocationID=T.LocationID FOR XML PATH(''),TYPE
        )
    .value('text()[1]','nvarchar(max)'),1,2,N''
    ) AS DivisionName
) AS TDIVName
CROSS APPLY
(
SELECT 
Stuff(
        (
            SELECT DISTINCT N', ' + BusinessUnitCode 
            FROM TempLocations AS TDIV
            WHERE TDIV.LocationID=T.LocationID FOR XML PATH(''),TYPE
        )
    .value('text()[1]','nvarchar(max)'),1,2,N''
    ) AS BusinessUnitCode
) AS TB
CROSS APPLY
(
SELECT 
Stuff(
        (
            SELECT DISTINCT N', ' + BusinessUnitName 
            FROM TempLocations AS TDIV
            WHERE TDIV.LocationID=T.LocationID FOR XML PATH(''),TYPE
        )
    .value('text()[1]','nvarchar(max)'),1,2,N''
    ) AS BusinessUnitName
) AS TBName

-- Drop table
DROP TABLE TempLocations

Convert row to columns in SQL Server

2 Answers2