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

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
Payal sathavaraWindows Developer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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 AdvisorCommented:
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 AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.