Solved

sql unpivot with dynamic column names and data values

Posted on 2013-11-08
3
1,888 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
3 Comments
 
LVL 40

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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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