Sql-server – How to convert rows values into different columns based on the values in a Column in SQL Server 2012

sql-server-2012

Scenario:

VendorID | OPTFIELD   |  VALUE  |   VDESC
---------+------------+---------+---------------------
7ELEVEN  | CHANNELAP  |   03    | Independents
7ELEVEN  | CUSTGROUP  | Metcash | Metcash
7ELEVEN  | SUBCHANNEL |   ING   | Independent Grocery

Solution Required:

Vendor_ID | level_2 |  level_2_desc| level_3 |         level_3_dec | level_4 | level_4_desc 
----------+---------+--------------+---------+---------------------+---------+--------------
7ELEVEN   |    03   | Independents |   ING   | Independent Grocery | Metcash |      Metcash

So I just want to convert the rows into columns depending on a particular scenario which is based on the value of one of the Columns(OPTFIELD)

Example:
– When OPTFIELD ='CHANNELAP' then results corresponding to 'CHANNELAP' that is Value and VDESC would appear in different columns as VALUE in level_2 and VDESC in level_2_desc

So, values in OPTFIELD columns is the condition on which VALUE and VDESC should be moved to different Columns.

IF 'OPTFIELD' = 'CHANNELAP'  then level_2(Value) and level_2_desc(VDESC) 
IF 'OPTFIELD' = 'CUSTGROUP'  then level_3(Value) and level_3_desc(VDESC) 
IF 'OPTFIELD' = 'SUBCHANNEL' then level_4(Value) and level_4_desc(VDESC)

So, the final result will be 1 row for VendorID = '7ELEVEN'

VendorID | level_2 | level_2_desc |level_3 |level_3_desc |level_4 |        level_4_desc 
---------+---------+--------------+--------+-------------+--------+-------------------- 
7ELEVEN  |      03 | Independents |Metcash |     Metcash |    ING | Independent Grocery

Best Answer

This is a typical case of a Pivot Table.

You can do it in standard SQL using the following query:

SELECT
    VendorID, 
    max(case when OPTFIELD = 'CHANNELAP'  then VALUE end) AS level_2,
    max(case when OPTFIELD = 'CHANNELAP'  then VDESC end) AS level_2_desc,
    max(case when OPTFIELD = 'CUSTGROUP'  then VALUE end) AS level_3,
    max(case when OPTFIELD = 'CUSTGROUP'  then VDESC end) AS level_3_desc,
    max(case when OPTFIELD = 'SUBCHANNEL' then VALUE end) AS level_4,
    max(case when OPTFIELD = 'SUBCHANNEL' then VDESC end) AS level_4_desc
FROM
    t
GROUP BY
    VendorID
ORDER BY
    VendorID ;

Assumption: (VendorID, OPTFIELD) is UNIQUE (and probably, the PRIMARY KEY of the table). This way, the max function will actually take the only actual value.

MS SQL server also has a specific PIVOT statement, but the syntax to use is a bit awkard when you want to have two different columns from each row:

SELECT 
    VendorID, 
    [CHANNELAP]   AS level_2, 
    [dCHANNELAP]  AS level_2_desc, 
    [CUSTGROUP]   AS level_3, 
    [dCUSTGROUP]  AS level_3_desc, 
    [SUBCHANNEL]  AS level_4,
    [dSUBCHANNEL] AS level_4_desc
FROM
(
   SELECT 
       VendorID, OPTFIELD, [VALUE] 
   FROM 
       t
   UNION
   SELECT 
       VendorID, 'd' + OPTFIELD, [VDESC]
   FROM 
       t
) AS SourceTable
PIVOT
(
    max(VALUE)
    FOR OPTFIELD IN (CHANNELAP, CUSTGROUP, SUBCHANNEL, dCHANNELAP, dCUSTGROUP, dSUBCHANNEL)
) AS PivotTable;

dbfiddle here

NOTE: I am not sure which of the queries is more efficient. You should experiment yourself with your actual data. The PIVOT syntax of MS SQL Server is probably more efficient when you're working with just one column to pivot. But your case is not exactly the one this statement was intended for (AFAIK).

Check also

Related Question