Create a Dynamic Column From Rows using SQL Query and set According to data

Payal sathavara
Payal sathavara used Ask the Experts™
on
I want create dynamic columns for 'Itg_Tiles_Grade' Rows data for production and column name like production + ' Itg_Tiles_Grade '  and set Quantity as per 'Itg_Tiles_Grade'.

now  also add one column for total of 'Itg_Tiles_Grade' as TotalProdction

then again CREATED dynamic columns for 'Itg_Tiles_Grade' Rows data for Quantity and column name like production + ' Itg_Tiles_Grade ' and data is calculated as per images
 
I have atttached table script as excel for data please just copy and paste in table.
Table Script :  Table-Script.sql

Table-Data.xlsx

Then also attched output file which i want.
output.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
CREATE TABLE [dbo].[Table_1](
	[StockTransferId] [numeric](18, 0) NULL,
	[CompanyMasterId] [numeric](18, 0) NULL,
	[BranchGodownMasterId] [numeric](18, 0) NULL,
	[DocumentNo] [varchar](50) NULL,
	[DocumentDate] [date] NULL,
	[Itg_Tiles_SizeInMM] [varchar](50) NULL,
	[Itg_Tiles_Design] [varchar](50) NULL,
	[Itg_Tiles_Grade] [varchar](50) NULL,
	[Quantity] [numeric](18, 2) NULL,
	[ProductionQty] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

Open in new window

+-----------------+-----------------+----------------------+------------+--------------+--------------------+------------------+-----------------+----------+---------------+
| StockTransferId | CompanyMasterId | BranchGodownMasterId | DocumentNo | DocumentDate | Itg_Tiles_SizeInMM | Itg_Tiles_Design | Itg_Tiles_Grade | Quantity | ProductionQty |
+-----------------+-----------------+----------------------+------------+--------------+--------------------+------------------+-----------------+----------+---------------+
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 300 X 600          | BERGAMO GRIS     |                 |     1250 |          0.00 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 300 X 600          | CANCUN BEIGE     | COM             |        8 |          5.06 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 300 X 600          | CANCUN BEIGE     | GOLD            |      150 |         94.94 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | BARSILONA        |                 |     5650 |         83.37 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | BARSILONA        | COM             |        6 |          0.09 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | BARSILONA        | GOLD            |     1116 |         16.47 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | BARSILONA        | UTL             |        5 |          0.07 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | DRAGO            |                 |       50 |         83.33 |
|           20294 |               2 |                    3 |        282 | 2018-12-21   | 396 X 396          | DRAGO            | GOLD            |       10 |         16.67 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BEIGE            |                 |     7335 |         90.66 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BEIGE            | COM             |       27 |          0.33 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BEIGE            | INTL            |      721 |          8.91 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BEIGE            | OTC             |        8 |          0.10 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BLANCO           |                 |    45990 |         96.33 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BLANCO           | INTL            |     1533 |          3.21 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BLANCO           | OTC             |      218 |          0.46 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BRANCA           | COM             |      280 |          8.26 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BRANCA           | INTL            |     3005 |         88.67 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BRANCA           | OTC             |      104 |          3.07 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BROWN            |                 |     7720 |         90.94 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BROWN            | COM             |       35 |          0.41 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | BROWN            | INTL            |      734 |          8.65 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | CUPRA            |                 |       45 |         91.84 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | CUPRA            | INTL            |        4 |          8.16 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | GRIGIO           |                 |     8970 |         90.91 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | GRIGIO           | COM             |       56 |          0.57 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | GRIGIO           | INTL            |      841 |          8.52 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | ROSSO            |                 |    43072 |         91.00 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | ROSSO            | INTL            |     3974 |          8.40 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 300X300X10MM       | ROSSO            | OTC             |      286 |          0.60 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 600X600X10MM       | CALDI BIANCO     |                 |      100 |         80.65 |
|           20296 |               1 |                    4 |        250 | 2018-12-22   | 600X600X10MM       | CALDI BIANCO     | COM             |       24 |         19.35 |
+-----------------+-----------------+----------------------+------------+--------------+--------------------+------------------+-----------------+----------+---------------+

Open in new window


+------------------+-----------------+-----------------------+------------+--------------+--------------------+------------------+------------------+-----------------+----------------+-----------------+----------------+----------------+------------------+------------+-----------+---------+----------+---------+---------+
| Stock TransferId | CompanyMasterId | BranchGodown MasterId | DocumentNo | DocumentDate | Itg_Tiles_SizeInMM | Itg_Tiles_Design | Production Blank | Production INTL | Production COM | Production GOLD | Production UTL | Production OTC | Production TOTAL | Qty  blank | Qty  INTL | Qty COM | Qty GOLD | Qty UTL | Qty OTC |
+------------------+-----------------+-----------------------+------------+--------------+--------------------+------------------+------------------+-----------------+----------------+-----------------+----------------+----------------+------------------+------------+-----------+---------+----------+---------+---------+
|            20294 |               2 |                     3 |        282 | 2018-12-21   | 300 X 600          | BERGAMO GRIS     |             1250 |               0 |              0 |               0 |              0 |              0 |             1250 |     100.00 |      0.00 |    0.00 |     0.00 |    0.00 |    0.00 |
|            20294 |               2 |                     3 |        282 | 2018-12-21   | 300 X 600          | CANCUN BEIGE     |                0 |               0 |              8 |             150 |              0 |              0 |              158 |       0.00 |      0.00 |    5.06 |    94.94 |    0.00 |    0.00 |
|            20294 |               2 |                     3 |        282 | 2018-12-21   | 396 X 396          | BARSILONA        |             5650 |               0 |              6 |            1116 |              5 |              0 |             6777 |      83.37 |      0.00 |    0.09 |    16.47 |    0.07 |    0.00 |
|            20294 |               2 |                     3 |        282 | 2018-12-21   | 396 X 396          | DRAGO            |               50 |               0 |              0 |              10 |              0 |              0 |               60 |      83.33 |      0.00 |    0.00 |    16.67 |    0.00 |    0.00 |
|            20296 |               1 |                     4 |        250 | 2018-12-22   | 300X300X10MM       | BEIGE            |             7335 |             721 |             27 |               0 |              0 |              8 |             8091 |      90.66 |      8.91 |    0.33 |     0.00 |    0.00 |    0.10 |
|            20296 |               1 |                     4 |        250 | 2018-12-22   | 300X300X10MM       | BLANCO           |            45990 |            1533 |              0 |               0 |              0 |            218 |            47741 |      96.33 |      3.21 |    0.00 |     0.00 |    0.00 |    0.46 |
|            20296 |               1 |                     4 |        250 | 2018-12-22   | 300X300X10MM       | BRANCA           |                0 |            3005 |            280 |               0 |              0 |            104 |             3389 |       0.00 |     88.67 |    8.26 |     0.00 |    0.00 |    3.07 |
|            20296 |               1 |                     4 |        250 | 2018-12-22   | 300X300X10MM       | BROWN            |             7720 |             734 |             35 |               0 |              0 |              0 |             8489 |      90.94 |      8.65 |    0.41 |     0.00 |    0.00 |    0.00 |
+------------------+-----------------+-----------------------+------------+--------------+--------------------+------------------+------------------+-----------------+----------------+-----------------+----------------+----------------+------------------+------------+-----------+---------+----------+---------+---------+

Open in new window

ascii tables by https://ozh.github.io/ascii-tables/
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
A TRADITIONAL "pivot" (a group by query that also uses case expressions) would look like this:
SELECT
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , MAX( DocumentDate )                                              as DocumentDate
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design

  , SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN ProductionQty END )  AS ProductionBlank
  , SUM( CASE WHEN Itg_Tiles_Grade = 'INTL' THEN ProductionQty END ) AS ProductionINTL
  , SUM( CASE WHEN Itg_Tiles_Grade = 'COM' THEN ProductionQty END )  AS ProductionCOM
  , SUM( CASE WHEN Itg_Tiles_Grade = 'GOLD' THEN ProductionQty END ) AS ProductionGOLD
  , SUM( CASE WHEN Itg_Tiles_Grade = 'UTL' THEN ProductionQty END )  AS roductionUTL
  , SUM( CASE WHEN Itg_Tiles_Grade = 'OTC' THEN ProductionQty END )  AS ProductionOTC
  , SUM( ProductionQty )                                             AS ProductionTOTAL

  , SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN Quantity END )       AS Qtyblank
  , SUM( CASE WHEN Itg_Tiles_Grade = 'INTL' THEN Quantity END )      AS QtyINTL
  , SUM( CASE WHEN Itg_Tiles_Grade = 'COM' THEN Quantity END )       AS QtyCOM
  , SUM (CASE WHEN Itg_Tiles_Grade = 'GOLD' THEN Quantity END)       AS QtyGOLD
  , SUM (CASE WHEN Itg_Tiles_Grade = 'UTL' THEN Quantity END)        AS QtyUTL
  , SUM (CASE WHEN Itg_Tiles_Grade = 'OTC' THEN Quantity END)        AS QtyOTC
FROM table_1
GROUP BY
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design
;

Open in new window

+-----------------+-----------------+----------------------+---------------------+--------------------+------------------+-----------------+----------------+---------------+----------------+--------------+---------------+-----------------+----------+---------+--------+---------+--------+--------+
| StockTransferId | CompanyMasterId | BranchGodownMasterId |    DocumentDate     | Itg_Tiles_SizeInMM | Itg_Tiles_Design | ProductionBlank | ProductionINTL | ProductionCOM | ProductionGOLD | roductionUTL | ProductionOTC | ProductionTOTAL | Qtyblank | QtyINTL | QtyCOM | QtyGOLD | QtyUTL | QtyOTC |
+-----------------+-----------------+----------------------+---------------------+--------------------+------------------+-----------------+----------------+---------------+----------------+--------------+---------------+-----------------+----------+---------+--------+---------+--------+--------+
|           20294 |               2 |                    3 | 21/12/2018 00:00:00 | 300 X 600          | BERGAMO GRIS     |            0.00 |                |               |                |              |               |            0.00 |     1250 |         |        |         |        |        |
|           20294 |               2 |                    3 | 21/12/2018 00:00:00 | 300 X 600          | CANCUN BEIGE     |                 |                |          5.06 |          94.94 |              |               |          100.00 |          |         |      8 |     150 |        |        |
|           20294 |               2 |                    3 | 21/12/2018 00:00:00 | 396 X 396          | BARSILONA        |           83.37 |                |          0.09 |          16.47 |         0.07 |               |          100.00 |     5650 |         |      6 |    1116 |      5 |        |
|           20294 |               2 |                    3 | 21/12/2018 00:00:00 | 396 X 396          | DRAGO            |           83.33 |                |               |          16.67 |              |               |          100.00 |       50 |         |        |      10 |        |        |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | BEIGE            |           90.66 |           8.91 |          0.33 |                |              |          0.10 |          100.00 |     7335 |     721 |     27 |         |        |      8 |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | BLANCO           |           96.33 |           3.21 |               |                |              |          0.46 |          100.00 |    45990 |    1533 |        |         |        |    218 |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | BRANCA           |                 |          88.67 |          8.26 |                |              |          3.07 |          100.00 |          |    3005 |    280 |         |        |    104 |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | BROWN            |           90.94 |           8.65 |          0.41 |                |              |               |          100.00 |     7720 |     734 |     35 |         |        |        |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | CUPRA            |           91.84 |           8.16 |               |                |              |               |          100.00 |       45 |       4 |        |         |        |        |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | GRIGIO           |           90.91 |           8.52 |          0.57 |                |              |               |          100.00 |     8970 |     841 |     56 |         |        |        |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 300X300X10MM       | ROSSO            |           91.00 |           8.40 |               |                |              |          0.60 |          100.00 |    43072 |    3974 |        |         |        |    286 |
|           20296 |               1 |                    4 | 22/12/2018 00:00:00 | 600X600X10MM       | CALDI BIANCO     |           80.65 |                |         19.35 |                |              |               |          100.00 |      100 |         |     24 |         |        |        |
+-----------------+-----------------+----------------------+---------------------+--------------------+------------------+-----------------+----------------+---------------+----------------+--------------+---------------+-----------------+----------+---------+--------+---------+--------+--------+

Open in new window

available as demo
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
In a dynamic form:
DECLARE @prd_cols AS NVARCHAR(MAX)
     ,  @qty_cols AS NVARCHAR(MAX)
     ,  @query  AS NVARCHAR(MAX)


select distinct
      case when Itg_Tiles_Grade IS NULL then 1 else 2 end as rn
    , case when Itg_Tiles_Grade IS NULL
           then 'SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN ProductionQty END )  AS Production_blank' 
           else 'SUM( CASE WHEN Itg_Tiles_Grade = ''' 
                + Itg_Tiles_Grade
                + ''' THEN ProductionQty END ) AS Production_'
                + Itg_Tiles_Grade
      end prd_cols
    , case when Itg_Tiles_Grade IS NULL
           then 'SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN Quantity END )  AS Quantityblank' 
           else 'SUM( CASE WHEN Itg_Tiles_Grade = ''' 
                + Itg_Tiles_Grade
                + ''' THEN Quantity END ) AS Quantity_'
                + Itg_Tiles_Grade
      end qty_cols
into #cols_temp
from table_1

SET @prd_cols = (SELECT ', ' + prd_cols
                  FROM #cols_temp
                  order by rn, prd_cols
                  FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
        
SET @qty_cols = (SELECT ', ' + qty_cols
                  FROM #cols_temp
                  order by rn, prd_cols
                  FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
        

set @query = 'SELECT
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , MAX( DocumentDate ) as DocumentDate
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design
  '
+ @prd_cols
+ ' 
  '
+ @qty_cols        
+ ' 
 FROM table_1
 GROUP BY
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design'
  
--select @query

execute(@query)

drop table #cols_temp;

Open in new window

The generated SQL looks like this:
SELECT
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , MAX( DocumentDate ) as DocumentDate
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design
  , SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN ProductionQty END )  AS Production_blank, SUM( CASE WHEN Itg_Tiles_Grade = 'COM' THEN ProductionQty END ) AS Production_COM, SUM( CASE WHEN Itg_Tiles_Grade = 'GOLD' THEN ProductionQty END ) AS Production_GOLD, SUM( CASE WHEN Itg_Tiles_Grade = 'INTL' THEN ProductionQty END ) AS Production_INTL, SUM( CASE WHEN Itg_Tiles_Grade = 'OTC' THEN ProductionQty END ) AS Production_OTC, SUM( CASE WHEN Itg_Tiles_Grade = 'UTL' THEN ProductionQty END ) AS Production_UTL 
  , SUM( CASE WHEN Itg_Tiles_Grade IS NULL THEN Quantity END )  AS Quantityblank, SUM( CASE WHEN Itg_Tiles_Grade = 'COM' THEN Quantity END ) AS Quantity_COM, SUM( CASE WHEN Itg_Tiles_Grade = 'GOLD' THEN Quantity END ) AS Quantity_GOLD, SUM( CASE WHEN Itg_Tiles_Grade = 'INTL' THEN Quantity END ) AS Quantity_INTL, SUM( CASE WHEN Itg_Tiles_Grade = 'OTC' THEN Quantity END ) AS Quantity_OTC, SUM( CASE WHEN Itg_Tiles_Grade = 'UTL' THEN Quantity END ) AS Quantity_UTL 
 FROM table_1
 GROUP BY
    StockTransferId
  , CompanyMasterId
  , BranchGodownMasterId
  , Itg_Tiles_SizeInMM
  , Itg_Tiles_Design

Open in new window


demo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial