0

This question already has an answer here:

I have this temp table named #DynamicDB, with below data.

Date     Actual_Prod    Forecast_Prod   MRB_Parts
01-04-18   1                 2             2
02-04-18   3                 2           NULL
03-04-18   0                 3           NULL
04-04-18   1                 4           NULL
05-04-18   1               NULL          NULL

I want this data to be converted into Column wise, as shown below.

List_Of_Date    01-04-18  02-04-18  03-04-18  04-04-18  05-04-18
Actual_Prod    1        3          0    1     1
Forecast_Prod      2        2          3    4    NULL
MRB_Parts      2       NULL       NULL  NULL     NULL

marked as duplicate by Paul White Jun 17 at 7:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Please consider that above request is for Dynamic columns(as dates vary) and not for static/fix columns(dates). – Pankaj Gupta Apr 24 '18 at 6:11
0

Using your test data

--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

I used a common table expression to union the following

with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)

which results in

| Col_Label     | DateValue  | DataValue |
|---------------|------------|-----------|
| Actual_Prod   | 2018-01-04 | 1         |
| Actual_Prod   | 2018-02-04 | 3         |
| Actual_Prod   | 2018-03-04 | 0         |
| Actual_Prod   | 2018-04-04 | 1         |
| Actual_Prod   | 2018-05-04 | 1         |
| ForeCast_Prod | 2018-01-04 | 2         |
| ForeCast_Prod | 2018-02-04 | 2         |
| ForeCast_Prod | 2018-03-04 | 3         |
| ForeCast_Prod | 2018-04-04 | 4         |
| ForeCast_Prod | 2018-05-04 | NULL      |
| MRB_Parts     | 2018-01-04 | 2         |
| MRB_Parts     | 2018-02-04 | NULL      |
| MRB_Parts     | 2018-03-04 | NULL      |
| MRB_Parts     | 2018-04-04 | NULL      |
| MRB_Parts     | 2018-05-04 | NULL      |

and then utilized that data in a dynamic PIVOT to produce the final result.
I also took the liberty of replacing null values with zeros.

Here is the full solution:


--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

--The actual query
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

--Create dynamic column names for PIVOT
SELECT @col = Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull = Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT CONVERT(VARCHAR(10), [DateValue], 121) AS PvtColumnName
    FROM #T
    ) AS SampleTable
ORDER BY PvtColumnName;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)
,cte_SampleTable as
(
select
Col_Label
,CONVERT(VARCHAR(10), [DateValue],121) 
        AS PvtColumnName
,DataValue
from UnionedData

)
SELECT Col_Label, ' + @colCoalesceNull + 'FROM cte_SampleTable
    PIVOT(Min(DataValue)
    FOR PvtColumnName IN (' + @col + ')) AS PivotSampleTable';
print @sql
EXEC sp_executesql @sql;

| Col_Label     | 2018-01-04 | 2018-02-04 | 2018-03-04 | 2018-04-04 | 2018-05-04 |
|---------------|------------|------------|------------|------------|------------|
| Actual_Prod   | 1          | 3          | 0          | 1          | 1          |
| ForeCast_Prod | 2          | 2          | 3          | 4          | 0          |
| MRB_Parts     | 2          | 0          | 0          | 0          | 0          |

Not the answer you're looking for? Browse other questions tagged or ask your own question.