Solved

sql unpivot with dynamic column names and data values

Posted on 2013-11-08
3
2,092 Views
Last Modified: 2013-11-08
I am trying to extract the column names and field value for that column for a given user for a table that can be dynamic in length


Password EmployeeNo AdminProgram SaleExpenseCodes Company Branch Department Personnel Security Nextfield Nextfield1 Nextfield2 etc...

barry22 98315 0 -1 0 0 0 -1 -1
blanke4 98166 0 -1 -1 -1 -1 -1 -1
bogart1 98284 0 -1 -1 -1 -1 -1 -1

Desired Result for employee 98315

FieldName        FieldValue

AdminProgram       0
SaleExpenseCodes -1
Company                 0
Branch                     0
Department            0
Personnel               -1
Security                  -1

I have figured out how to extract the column names to a variable as follows

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.Secure')
           and c.name<>'employeeno' 
           for xml path('')), 1, 1, '')

Open in new window


but I am having trouble folding that into the UNPIVOT statement.  Can anyone point me in the right direction.
0
Comment
Question by:josephpmo
[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
3 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39635288
try like this.
declare @cols nvarchar(max),@query nvarchar(max)
 select  @cols = stuff(( select '],[' + column_name
                           from information_schema.columns
                          where table_name = 'Secure'
                             and column_name not in ('Password','EmployeeNo')
                            for xml path('')), 1, 2, '') + ']'

select @query = '
select EmployeeNo,ColumnName,ColumnValue
  from Secure
 unpivot (ColumnValue for ColumnName in (' + @cols + ')) up'

exec (@query)

Open in new window


http://sqlfiddle.com/#!3/1ab45/12
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39635290
This approach does not use the UNPIVOT feature but achieves the same outcome (and is arguably more efficient). The technique uses CROSS APPLY and VALUES instead.

Here this technique is quite convenient as all we need to do is repeat the column name, once as a label, and again as the field reference to access the relevant value (see lines 9-15 below). Once the dynamic sql is formed it would look like this IF it was formatted for readability:
SELECT
        employeeno
      , ID
      , FieldName
      , FieldValue
FROM dbo.secure
        CROSS APPLY (
                     VALUES
                                  ('AdminProgram', [AdminProgram])
                                , ('SaleExpenseCodes', [SaleExpenseCodes])
                                , ('Company', [Company])
                                , ('Branch', [Branch])
                                , ('Department', [Department])
                                , ('Personnel', [Personnel])
                                , ('Security', [Security])
                    ) AS ca1 (FieldName, FieldValue)
;

Open in new window

To produce that query dynamically I used the following:
DECLARE @SQL AS NVARCHAR(MAX)

SELECT @SQL =
                'SELECT employeeno, ID, FieldName, FieldValue FROM dbo.secure'
              + ' CROSS APPLY ( VALUES '
              + stuff(
                            (
                             SELECT ',('''+ C.name + ''',' + quotename(C.name) + ')'
                             FROM sys.columns c
                             WHERE c.object_id = OBJECT_ID('dbo.Secure')
                             AND c.name NOT IN ('ID','employeeno')

                    AND c.system_type_id = 56 /* all int fields in this example */

                             FOR xml path('')
                            )
                     , 1, 1, '')
              + ' ) AS ca1 (FieldName,FieldValue)'

SELECT @SQL

Open in new window

Please note line 13 in that. You need to take care that the column of field values will be of the same type or compatible types, and as your example shows integers I have simply extended the sys.columns subquery to only list integer fields. This may need modification for the actual circumstances.

based on the data contained in the question, the dynamically produced query produced the following result:
| EMPLOYEENO |    ID |        FIELDNAME | FIELDVALUE |
|------------|-------|------------------|------------|
|    barry22 | 98315 |     AdminProgram |          0 |
|    barry22 | 98315 | SaleExpenseCodes |         -1 |
|    barry22 | 98315 |          Company |          0 |
|    barry22 | 98315 |           Branch |          0 |
|    barry22 | 98315 |       Department |          0 |
|    barry22 | 98315 |        Personnel |         -1 |
|    barry22 | 98315 |         Security |         -1 |
|    blanke4 | 98166 |     AdminProgram |          0 |
|    blanke4 | 98166 | SaleExpenseCodes |         -1 |
|    blanke4 | 98166 |          Company |         -1 |
|    blanke4 | 98166 |           Branch |         -1 |
|    blanke4 | 98166 |       Department |         -1 |
|    blanke4 | 98166 |        Personnel |         -1 |
|    blanke4 | 98166 |         Security |         -1 |
|    bogart1 | 98284 |     AdminProgram |          0 |
|    bogart1 | 98284 | SaleExpenseCodes |         -1 |
|    bogart1 | 98284 |          Company |         -1 |
|    bogart1 | 98284 |           Branch |         -1 |
|    bogart1 | 98284 |       Department |         -1 |
|    bogart1 | 98284 |        Personnel |         -1 |
|    bogart1 | 98284 |         Security |         -1 |


    CREATE TABLE Secure
    	([employeeno] varchar(7), [ID] int, [AdminProgram] int, [SaleExpenseCodes] int, [Company] int, [Branch] int, [Department] int, [Personnel] int, [Security] int)
    ;
    	
    INSERT INTO Secure
    	([employeeno], [ID], [AdminProgram], [SaleExpenseCodes], [Company], [Branch], [Department], [Personnel], [Security])
    VALUES
    	('barry22', 98315, 0, -1, 0, 0, 0, -1, -1),
    	('blanke4', 98166, 0, -1, -1, -1, -1, -1, -1),
    	('bogart1', 98284, 0, -1, -1, -1, -1, -1, -1)
    ;
: http://sqlfiddle.com/#!3/8cd75/22

Open in new window

For more on the cross apply/values technique see:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
which explores the efficiency of the technique against unpivot, showing lower CPU usage and slightly faster.
0
 

Author Closing Comment

by:josephpmo
ID: 39635306
Thank you both.  I will need to go study cross apply as I am not familiar at all with that
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 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