?
Solved

sql unpivot with dynamic column names and data values

Posted on 2013-11-08
3
Medium Priority
?
2,470 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 41

Assisted Solution

by:Sharath
Sharath earned 1000 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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

579 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