Solved

unpivot calculated columns to rows, unkown column names

Posted on 2014-12-18
2
138 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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