Post reply

Extract Column Names and Field Value from a table using UNPIVOT

  • November 8, 2013 at 3:41 pm

    #279749

    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

    PasswordEmployeeNoAdminProgramSaleExpenseCodesCompanyBranchDepartmentPersonnelSecurity Nextfield Nextfield1 Nextfield2 etc...

    barry22983150-1000-1-1

    blanke4981660-1-1-1-1-1-1

    bogart1982840-1-1-1-1-1-1

    Desired Result for employee 98315

    FieldName Value

    AdminProgram 0

    SaleExpenseCodes -1

    Company 0

    Branch 0

    Department 0

    Personnel -1

    Security -1

    I have managed to construct the code to extract the list of field names from the table as shown below but I am struggling with then using the data in the UNPIVOT

    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, '')

  • November 8, 2013 at 5:25 pm

    #1665266

    Maybe the CROSS APPLY approach to unpivot can help you. Even to create the dynamic code for N number of columns would be easier than the built-in UNPIVOT function.

    Reference: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    WITH SampleD( Password,EmployeeNo,AdminProgram,SaleExpenseCodes,Company,Branch,Department,Personnel,Security) AS(

    SELECT 'barry22','98315',0,-1,0,0,0,-1,-1 UNION ALL

    SELECT 'blanke4','98166',0,-1,-1,-1,-1,-1,-1 UNION ALL

    SELECT 'bogart1','98284',0,-1,-1,-1,-1,-1,-1)

    SELECT Name, Value

    FROM SampleD s

    CROSS APPLY(VALUES(AdminProgram,'AdminProgram'),

    (SaleExpenseCodes,'SaleExpenseCodes'),

    (Company,'Company'),

    (Branch,'Branch'),

    (Department,'Department'),

    (Personnel,'Personnel'),

    (Security,'Security'))x(Value, Name)

    WHERE EmployeeNo = '98315'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply