Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2610
  • Last Modified:

sql unpivot with dynamic column names and data values

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
josephpmo
Asked:
josephpmo
2 Solutions
 
SharathData EngineerCommented:
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
 
PaulCommented:
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
 
josephpmoAuthor Commented:
Thank you both.  I will need to go study cross apply as I am not familiar at all with that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now