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

Question

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

Answer 1

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

• Convert Rows to columns using 'Pivot' in SQL Server, for the "typical" PIVOT case;
• PIVOT on two or more fields in SQL Server, although the case is not exactly yours.
• TSQL PIVOT multiple columns, which is very close to your case, and offers a slightly different alternative