Solved

unpivot calculated columns to rows, unkown column names

Posted on 2014-12-18
2
127 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 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