CODEWITHSUNDEEP
sqlsql-server
user3605194
user3605194

Reputation: 51

SQL Server Pivot/Map column values to rows

I've made schema changes/improvements to a table, but I need to ensure that I don't lose any existing data and it is 'migrated' across to the new schema and conforms to its design.

The existing schema is designed as follows:

ID  FK_ID  ShowChartX ShowChartY ShowChartZ
--  -----  ---------- ---------- ----------
1     2         1          0          1

The columns of ShowChartX, ShowChartY, and ShowChartZ are of type BIT (boolean).

I've now created a standalone table that keeps a record/reference of each chart. Each Chart record has a Chart_ID - the aim here is to use an ID for each type of chart instead of horizontally scaling a 'ShowChart' column for each type of chart going forward. Essentially, I would like to map all columns of 'ShowChart' to their actual Chart_ID key in the table I mention below:

The new schema would look like this:

ID  FK_ID  Chart_ID
--  -----  --------
 1    2       1
 2    2       2

I've started looking at Pivot/Unpivot, but I'm not sure if it's the correct operation. Could anyone please point me in the right direction here? Thanks in advance!

Upvotes: 0

Views: 232

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

Thanks to @gotqn for the table definition and values.

The same result can be achieved using CROSS APPLY. Here, I am deriving Chart_Id based on ChartType, as I don't have the table reference for ChartTypes. Ideally, You can join with ChartTypes to get the corresponding Chart_Id. 

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[FK_ID] INT
   ,[ShowChartX] BIT
   ,[ShowChartY] BIT
   ,[ShowChartZ] BIT
);

INSERT INTO @DataSource ([ID], [FK_ID], [ShowChartX], [ShowChartY], [ShowChartZ])
VALUES (1, 2, 1, 0, 1);

SELECT id, 
       fk_id, 
       CASE charttype 
         WHEN 'ChartX' THEN 1 
         WHEN 'ChartY' THEN 3 
         WHEN 'ChartZ' THEN 2 
       END AS Chart_ID 
FROM   @DataSource 
       CROSS apply (VALUES('ChartX', showchartx), 
                          ('ChartY', showcharty), 
                          ('ChartZ', showchartz)) AS t(charttype, isavailable) 
WHERE  isavailable <> 0;

Result set

+----+-------+----------+
| ID | FK_ID | Chart_ID |
+----+-------+----------+
|  1 |     2 |        1 |
|  1 |     2 |        2 |
+----+-------+----------+

Upvotes: 2

gotqn
gotqn

Reputation: 43636

This will UNPIVOT the data. You can also, join the charts table by name in order to get the chart_id and check for differences with the new table:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[FK_ID] INT
   ,[ShowChartX] BIT
   ,[ShowChartY] BIT
   ,[ShowChartZ] BIT
);

INSERT INTO @DataSource ([ID], [FK_ID], [ShowChartX], [ShowChartY], [ShowChartZ])
VALUES (1, 2, 1, 0, 1);

SELECT [ID]
      ,[FK_ID]
      ,[column] AS [chart_name]
FROM @DataSource DS
UNPIVOT
(
    [value] FOR [column] IN ([ShowChartX], [ShowChartY], [ShowChartZ])
) UNPVT
WHERE [value] = 1;

enter image description here

For checking for differences it's pretty easy to use EXCEPT - for example:

SELECT *
FROM T1
EXCEPT
SELECT *
FROM T2;

to get records that are not including in T2 but in T1 and then the reverse:

SELECT *
FROM T2
EXCEPT
SELECT *
FROM T1;

Upvotes: 2

Related Questions