Solved

unpivot calculated columns to rows, unkown column names

Posted on 2014-12-18
2
110 Views
Last Modified: 2014-12-23
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

0
Comment
Question by:Scarlett72
2 Comments
 

Author Comment

by:Scarlett72
ID: 40508038
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

0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40508127
I fixed your code. Please note that I have adjusted it for TempDB..#myTable. Remove TempDB from sys.columns and sys.index_columns for your database:
SELECT *
FROM #mytable
UNPIVOT (
    value FOR code IN (
        [Metric1], [Metric2], [Metric3]
    )
) unpiv
                                          
DECLARE @table_name SYSNAME
SELECT @table_name = 'TempDB..#mytable'
 
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT *
FROM ' + @table_name + '
UNPIVOT (
    value FOR code IN (
        ' + STUFF((
    SELECT ', [' + c.name + ']'
    FROM TempDB.sys.columns c WITH(NOLOCK)
    LEFT JOIN (
        SELECT i.[object_id], i.column_id
        FROM TempDB.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('TempDB..#mytable')
        AND i.[object_id] IS NULL
		AND c.name NOT IN('PeriodType', 'PeriodValue', 'AgentNo')
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    )
) unpiv'
 
PRINT @SQL
EXEC sys.sp_executesql @SQL

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now