Help to Convert Rows to Column in Master Child Table Level

Question

User-1137493631 posted

All,

I would need your help to convert Rows to Column in the Master and Child label level. It would be great if i get some query to do this. thanks for your Help on this.

This the Master Table

Master Table
Id	TaxInfoId	TaxInfoValue
1	101	500
1	102	4000
1	103	10
2	104	20
2	101	800

This is Child Table of Tax Info

ChildTable
TaxInfoId	TaxInfoDesc
101	taxHeader1
102	taxHeader2
103	taxHeader3
104	taxHeader4

The Result that i'm expecting is, 

Result
Id	taxHeader1	taxHeader2	taxHeader3	taxHeader4
1	500	4000	10
2	800			800

Note : The TaxInfoId and TaxInfo would have dynamic Values( we should not be  hard code column list in query)

Answer 1

User-62323503 posted

You need to implement dynamic pivot on result set

Refer below post to learn how to implement dynamic pivot

http://www.itdeveloperzone.com/2011/01/pivot-with-dynamic-columns.html

Answer 2

User1559292362 posted

Hi thirumaran007,

I would need your help to convert Rows to Column in the Master and Child label level. It would be great if i get some query to do this. thanks for your Help on this.

As sandeepmitt said, we could use dynamic pivot to implement it, and I create a demo as below for your reference.

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(TaxInfoDesc)
FROM (SELECT TaxInfoDesc FROM ChildTable) AS t
 
SET @DynamicPivotQuery = 
  N'SELECT Id, ' + @ColumnName + '
    FROM (select A.id,a.TaxInfoValue,B.TaxInfoDesc from MasterTable2 A, ChildTable B where a.TaxInfoId = b.TaxInfoId) as x
    pivot (MAX(TaxInfoValue) 
          FOR TaxInfoDesc IN (' + @ColumnName + ')) AS PVTTable'

print @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Best regards,

Cole Wu