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.
josephpmoCFOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
josephpmoCFOAuthor Commented:
Thank you both.  I will need to go study cross apply as I am not familiar at all with that
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.