SQL Server 2008 pivoting table with unknown column name

Question

I am having trouble to pivot the following

    FirstName    LastName
    ----------------------
    Talyor       Swift
    Bruno        Mars

to the following

    ColumnName    ColumnValue
    --------------------------
    FirstName     Talyor
    LastName      Swift
    FirstName     Bruno
    LastName      Mars

I don't have any clue how to start this without hardcoding it especially the way to retrieve the column name from the system.

**Column Name in the source table is not given

@bluefeet yes column names is unknown, which could be anything — swordgit, Jul 25 '17 at 18:27

John Cappelletti · Accepted Answer · 2017-07-27T14:08:09.173

1

Here is a "dynamic" approach which is accomplished via XML.

Clearly UNPIVOT would be more performant

Example

Select C.*
      ,ColumnOrder = D.ORDINAL_POSITION
 From  YourTable A
 Cross Apply (Select XMLData = cast((Select A.* for XML Raw) as xml) ) B
 Cross Apply (
                Select Item   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row') as n(r)
                 Cross Apply n.r.nodes('./@*') AS B(attr)
             ) C
 Join INFORMATION_SCHEMA.COLUMNS D on D.Table_Name='YourTable' and D.Column_Name=C.Item

Returns

Item        Value   ColumnOrder
FirstName   Talyor  1
LastName    Swift   2
FirstName   Bruno   1
LastName    Mars    2

EDIT - Dynamic UnPivot

Declare @SQL varchar(max) ='
Select ColumnOrder = D.ORDINAL_POSITION
      ,Item
      ,Value
 From  YourTable
 UnPivot (Value for Item in ('+Stuff((Select ',' +QuoteName(Name) 
                                       From  sys.columns 
                                       Where objecT_id = OBJECT_ID('YourTable') 
                                       For XML Path ('')),1,1,'')
                             +')) as UnPiv
 Join INFORMATION_SCHEMA.COLUMNS D on D.Table_Name=''YourTable'' and D.Column_Name=UnPiv.Item
'
--Print @SQL
Exec(@SQL)

edited Jul 27 '17 at 14:08

answered Jul 25 '17 at 18:35

John Cappelletti

79,615
7
44
66

Interesting but extremely nonreadable, nonmanageble. I wouldn't do it like that definitely. – Giorgi Nakeuri Jul 25 '17 at 18:38
In the first line of the code, if I don't know how many columns are there in the table and what are their names. What should be done to make that work? – swordgit Jul 25 '17 at 18:40
@swordgit That is just a table variable used for demonstrative purposes. Remove everything above the SELECT and replace (at)YourTable with your actual table name, view, or subquery. – John Cappelletti Jul 25 '17 at 18:41
@swordgit Simplified the example – John Cappelletti Jul 25 '17 at 18:45
@JohnCappelletti Thank you it works! However, I am wondering if it is possible accomplish without using XML? – swordgit Jul 25 '17 at 18:48
@swordgit Sure, but you would need to go dynamic. – John Cappelletti Jul 25 '17 at 18:49
@swordgit here is a link to dynamic unpivot https://stackoverflow.com/questions/21996988/unpivot-all-columns-in-a-sql-table – John Cappelletti Jul 25 '17 at 18:53
@JohnCappelletti Would you mind to give an example on that too? – swordgit Jul 25 '17 at 18:54
@swordgit See EDIT - Dynamic UnPivot – John Cappelletti Jul 25 '17 at 19:03
@JohnCappelletti in the XML example, why do we put the condition "Where attr.value('local-name(.)','varchar(100)') not in ('FieldsTo','Exclude')", What does the FieldsTo and Exclude do? – swordgit Jul 26 '17 at 15:14
@swordgit Just an option to exclude certain fields (if desired). Can be removed – John Cappelletti Jul 26 '17 at 15:15
@JohnCappelletti If I want to add the order position of that column in its table, would that be only take one more cross apply? – swordgit Jul 27 '17 at 13:35
@swordgit Updated both options – John Cappelletti Jul 27 '17 at 14:08
@JohnCappelletti Thank you! but the .Item does not work, I am using SQL server2008 – swordgit Jul 31 '17 at 14:31
@swordgit Not sure what you mean. Both options are compatible with 2008. – John Cappelletti Jul 31 '17 at 14:37
@JohnCappelletti It gave me error saying "Invalid column name 'Item'." I guess it is thinking that item is one of the columns – swordgit Jul 31 '17 at 14:43
@swordgit Assuming you are running the dynamic version. This will execute on virtually any table. Just make sure you replaced the 3 "YourTable" with your actual table name. Can't seem to find a SQL Fiddle that supports 2008 – John Cappelletti Jul 31 '17 at 14:53
@swordgit Do you have an issue with the XML version? – John Cappelletti Jul 31 '17 at 14:55
@JohnCappelletti Yes, basically when I did a Select * from anytable.item it does not work – swordgit Jul 31 '17 at 14:58
@swordgit That wouldn't work in my world either. The code provided will unpivot virtually any table. ITEM does not exist unless you save the results into another table, view, or subquery. Now, I'm not 100% clear on what your ultimate goal is. – John Cappelletti Jul 31 '17 at 15:08

score -1 · Answer 2 · answered Jul 25 '17 at 18:31

-1

From what I see in the question this should work:

select 'firstname', firstname from table
union all
select 'lastname', lastname from table

If the column names are not known you can select them from sys.columns table, make a sql string and use exec function to execute sql string.

answered Jul 25 '17 at 18:31

Giorgi Nakeuri

35,155
8
47
75

but sys.columns gave me all columns from all tables, what if I only need the column just for that table? – swordgit Jul 25 '17 at 18:42
sys.columns table has a object_id column. sys.tables has also that column, so you can filter by it. – Giorgi Nakeuri Jul 25 '17 at 18:49

SQL Server 2008 pivoting table with unknown column name

2 Answers2