CODEWITHSUNDEEP
sqlsql-serverpivotdynamic-pivot
tomfbsc
tomfbsc

Reputation: 45

Convert Rows to Columns SQL

I am trying to convert rows to columns in SQL server. I am trying to convert the value's a product gets while being tested during quality. I have tried the pivot function but having trouble doing so as the same values do get repeated and it can not be easily sorted into rows. The table I am trying to pivot holds ~30K data row's so hoping to find a dynamic solution for this.

The maximum number of new columns is 30 but sometimes a product doesn't get tested as much so it can be less. The new column would be based off my inspection_unit_number field. Is this possible to achieve in SQL

Current data

What I hope to achieve

Current Attempt

SELECT BATCH , characteristic, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
from
(
  select inspection_lot ,node_number    ,characteristic ,inspector  ,inspection_unit_number ,start_date ,measured_value ,original_value ,material_no    ,batch

  from stg.IQC_Tensile_TF
) d


pivot
(
  max(measured_value)
  for 
  INSPECTION_UNIT_NUMBER in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
) piv;

Upvotes: 0

Views: 521

Answers (1)

Paulo Fernando
Paulo Fernando

Reputation: 3660

You will have to go for a dynamic query, check if this will suit your needs.

I created a common table expression to be able to use distinct and then order by in the stuff function:

DECLARE @QUERY NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)

WITH cte_unique_inspection_unit_number AS
(
SELECT DISTINCT QUOTENAME('TestResults' + CAST(inspection_unit_number AS VARCHAR)) TestResultsN, 
       inspection_unit_number
FROM IQC_Tensile_TF
)

SELECT @Columns = STUFF((SELECT ', ' + TestResultsN
            FROM cte_unique_inspection_unit_number
            ORDER BY inspection_unit_number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,''),

        @query = 'SELECT batch, node_number, characteristic, ' + @Columns + ' from 
            (
                select batch,
                       node_number,
                       characteristic,
                       measured_value,
                       ''TestResults'' + CAST(inspection_unit_number AS VARCHAR) TestResultsN
                from IQC_Tensile_TF
           ) x
            pivot 
            (
                 max(measured_value)
                for TestResultsN in (' + @Columns + ')
            ) p '

EXEC(@query)

To view the execution in fiddle:

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=7898422e4422faacb25d7f3c2285f14a

If you find my answer useful, i would appreciate if you vote up and mark as accepted =D

Upvotes: 1

Related Questions