asked on

unpivot calculated columns to rows, unkown column names

Hi, I have a table similar to the one below, I need to unpivot the table to that there would be 4 column, and a row would look like
The other issue, I'm facing is that there is an unknown number of rows, so I will somehow have to create a dynamic comma delimited list of column names from the table.
If anyone has a script to do this would be super appreciated!
~thank you in advance

PeriodType, PeriodValue, AgentNo, Metric, Value
Year 2014 EY57627 Metric1 1833
Year 2014 EY57627 Metric2 609.87
Year 2014 EY57627 Metric3 0.804878

     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
         DROP TABLE #mytable

--===== Create the test table with 
 CREATE TABLE #mytable 
        (
        PeriodType Varchar(50),
        PeriodValue varchar(50),
        AgentNo varchar(10),
        Metric1 varchar(50),
        Metric2 varchar(50),
        Metric3 varchar(50)
        )
       
       insert into #mytable 
       (PeriodType, PeriodValue, AgentNo, Metric1, Metric2, Metric3)
 SELECT 'Year','2014','EY57627','1833','609.87','0.804878' UNION ALL
SELECT 'Year','2014','EY37480','11784','401.413','0.833333' UNION ALL
SELECT 'Year','2014','EY37715','0','0','0' UNION ALL
SELECT 'Year','2014','6069748','8242','479.842','0.857542' UNION ALL
SELECT 'Year','2014','ex51539','0','0','0' UNION ALL
SELECT 'Year','2014','6071493','4833','594.724','0.7866' UNION ALL
SELECT 'Year','2014','EY58441','0','0','0' UNION ALL
SELECT 'Year','2014','EY46081','0','0','0' UNION ALL
SELECT 'Year','2014','EY38615','910','729.865','0.707865' UNION ALL
SELECT 'Year','2014','EY38864','3786','402.036','0.869674' UNION ALL
SELECT 'Year','2014','EY35608','824','886.701','0.791667' UNION ALL
SELECT 'Year','2014','EY53571','37','904.568','0.75' UNION ALL
SELECT 'Year','2014','EY11014','0','0','0' UNION ALL
SELECT 'Year','2014','EY13297','537','583.872','0.877193' UNION ALL
SELECT 'Year','2014','6010922','597','607.787','0.770833' UNION ALL
SELECT 'Year','2014','6027444','5255','299.85','0' UNION ALL
SELECT 'Year','2014','6027814','5830','678.576','0.709125' UNION ALL
SELECT 'Year','2014','EY54924','801','600.402','0.631579' UNION ALL
SELECT 'Year','2014','EY35642','2693','613.134','0.773913' UNION ALL
SELECT 'Year','2014','EY57364','0','0','0' UNION ALL
SELECT 'Year','2014','6072995','707','544.037','0.766667' UNION ALL
SELECT 'Year','2014','EY24392','9110','392.218','0.817299' UNION ALL
SELECT 'Year','2014','EY44314','5908','734.806','0.808905' UNION ALL
SELECT 'Year','2014','EY39344','7352','520.189','0.820922' UNION ALL
SELECT 'Year','2014','EX89538','5931','423.479','0.75'

Open in new window

Scarlett72

ASKER

Ok, I came across this script below, but I am getting the following error, is there any way to resolve this?

Query generated:

SELECT *
FROM dbo.t_OutlierCalc_BM
UNPIVOT (
    value FOR code IN (
        [PeriodType], [PeriodValue], [emp_no], [CallsHandled], [AverageHandlingTime], [CsatTop2], [CsatBottom2], [ResolutionTop2], [ResolutionBottom2], [FCR_7], [FCR_7_WithTransfer]
    )
) unpiv

Open in new window

The type of column "PeriodValue" conflicts with the type of other columns specified in the UNPIVOT list.

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.t_OutlierCalc_BM'
 
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT *
FROM ' + @table_name + '
UNPIVOT (
    value FOR code IN (
        ' + STUFF((
    SELECT ', [' + c.name + ']'
    FROM sys.columns c WITH(NOLOCK)
    LEFT JOIN (
        SELECT i.[object_id], i.column_id
        FROM sys.index_columns i WITH(NOLOCK)
        WHERE i.index_id = 1
    ) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
    WHERE c.[object_id] = OBJECT_ID(@table_name)
        AND i.[object_id] IS NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    )
) unpiv'
 
PRINT @SQL
EXEC sys.sp_executesql @SQL

Open in new window

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial