Share via

Create Dynamic Rows based on column data

Punnet Patel 21 Reputation points
2023-04-07T04:29:32.2533333+00:00

We want to replicate rows and populate product code from mapping table.

My sample table structure is as below.

We have #tb_RawData table in which we received from another team. We have #tb_productmapping mapping table in which we has product type and product code.

To populate [Product Type] in final Output we want to match the column header of the #tb_RawData with the #tb_productmapping (column product) eg. We match the column header [Elect Prd] of #tb_RawData with the [Product[ column of #tb_productmapping and get the [Product Type] as 'Electronic'

We want to replicate all rows from #tb_RawData and populate product code in our #tb_final table. Also I have attached the screen shot of RawData, Mapping Table, Final Output.

I am thinking of writing loop for this but we have around 10000 rows and 50 columns in raw data table Is there any other better way to solve this problem

---------------SQL Scripts /* RAW DATA */ IF OBJECT 1D ("tempdb..#tb RawData') IS NOT NULL DROP TABLE #tb_RawData CREATE TABLE #tb_RawData ([Customer] VARCHAR (255), [Books prd] TINYINT, [Elect Prd] TINYINT, [Food Prd] TINYINT) INSERT INTO #tb_RawData values (1,11,21,0) INSERT INTO #tb_RawData values (2,0,0,31) --select * from #tb_RawData

/* Mapping Table */ IF OBJECT ID('tempdb..#tb productmapping') IS NOT NULL DROP TABLE #tb_productmapping create table #tb_productmapping ( product varchar (255), producttype varchar (255) ) insert into #tb_productmapping values ('Books prd', 'Books') insert into #tb_productmapping values ('Elect Prd','Electronic') insert into #tb_productmapping values ('Food Prd','Food') --select * from #tb_productmapping

/* Final output */ IF OBJECT ID('tempdb..#tb_final') IS NOT NULL DROP TABLE #tb_final create table #tb_final (Customer varchar (255), [Product Type] varchar (255), [Books prd] TINYINT, [Elect Prd] TINYINT, [Food Prd] TINYINT) insert into #tb_final values (1,'Books',11,0,0) insert into #tb_final values (1,'Electronic',0,21,0) insert into #tb_final values (1,'Food',0,0,0) insert into #tb_final values (2, 'Books',0,0,0) insert into #tb_final values (2,'Electronic',0,0,0) insert into #tb_final values (2, 'Food',0,0,31) --select * from #tb_final

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2023-04-07T09:33:39.66+00:00

    Hi @Punnet Patel How about this?

    DECLARE @sql_str VARCHAR(MAX)
DECLARE @column_header VARCHAR(MAX)
DECLARE @CaseWhen_str VARCHAR(MAX)=''

SELECT @column_header = ISNULL(@column_header + ',','') + QUOTENAME(product) 
FROM #tb_productmapping GROUP BY product
--PRINT @column_header
SELECT @CaseWhen_str = @CaseWhen_str + ',CASE WHEN Product_Amount='+QUOTENAME(product)+' THEN Product_Amount ELSE 0 END AS ' + QUOTENAME(product)
FROM #tb_productmapping GROUP BY product
--PRINT @CaseWhen_str

SET @sql_str = '
SELECT C.Customer,ProductType '+@CaseWhen_str+'
FROM (SELECT *FROM #tb_RawData UNPIVOT(Product_Amount FOR Product IN ('+@column_header+'))U) C 
JOIN #tb_productmapping P ON C.Product =P.product
JOIN #tb_RawData R ON C.Customer=R.Customer'
--PRINT (@sql_str)
EXEC (@sql_str)
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 25,651 Reputation points
    2023-04-07T06:18:07.44+00:00

    Hi @Punnet Patel Try this dynamic query:

    DECLARE @sql_str VARCHAR(MAX)
DECLARE @column_header VARCHAR(MAX)
DECLARE @Isnull_str VARCHAR(MAX)=''

SELECT @column_header = ISNULL(@column_header + ',','') + QUOTENAME(product) 
FROM #tb_productmapping GROUP BY product
--PRINT @column_header
SELECT @Isnull_str = @Isnull_str + ',ISNULL('+QUOTENAME(product)+',0) AS ' + QUOTENAME(product)
FROM #tb_productmapping GROUP BY product
--PRINT @Isnull_str

SET @sql_str = '
;WITH CTE AS
(
SELECT Customer,P.producttype,U.Product,Product_Amount 
FROM #tb_RawData UNPIVOT(Product_Amount FOR Product IN ('+ @column_header +'))U
JOIN #tb_productmapping P ON U.Product =P.product
)
SELECT Customer,ProductType'+@Isnull_str+'
FROM CTE PIVOT(SUM(Product_Amount) FOR Product IN ('+ @column_header +'))P
ORDER BY Customer'
--PRINT (@sql_str)
EXEC (@sql_str)

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    2 people found this answer helpful.
  2. Punnet Patel 21 Reputation points
    2023-04-07T04:31:48.2566667+00:00

    Added in Rich Text Format

    IF OBJECT 1D ("tempdb..#tb RawData') IS NOT NULL DROP TABLE #tb_RawData CREATE TABLE #tb_RawData (

    [Customer] VARCHAR (255), [Books prd] TINYINT, [Elect Prd] TINYINT, [Food Prd] TINYINT ) INSERT INTO #tb_RawData values (1,11,21,0) INSERT INTO #tb_RawData values (2,0,0,31) --select * from #tb_RawData

    IF OBJECT ID('tempdb..#tb productmapping') IS NOT NULL DROP TABLE #tb_productmapping create table #tb_productmapping ( product varchar (255), producttype varchar (255) ) insert into #tb_productmapping values ('Books prd', 'Books')

    insert into #tb_productmapping values ('Elect Prd','Electronic') insert into #tb_productmapping values ('Food Prd','Food') --select * from #tb_productmapping

    IF OBJECT ID('tempdb..#tb_final') IS NOT NULL DROP TABLE #tb_final create table #tb_final (Customer varchar (255), [Product Type] varchar (255), [Books prd] TINYINT, [Elect Prd] TINYINT, [Food Prd] TINYINT)

    insert into #tb_final values (1,'Books',11,0,0) insert into #tb_final values (1,'Electronic',0,21,0) insert into #tb_final values (1,'Food',0,0,0)

    insert into #tb_final values (2, 'Books',0,0,0) insert into #tb_final values (2,'Electronic',0,0,0) insert into #tb_final values (2, 'Food',0,0,31) --select * from #tb_final

    0 comments