0

There is a single row of data in a table that will have a varying number of columns named C1, C2, C3.. etc. I can locate that row but i want to un-pivot those dynamic number of columns values into a single column result... I have researched a ton on pivot/unpivot stuff but all examples i've found don't seem to handle the dynamic number of columns in the results.

Native Results:

Col 1 Col 2 Col 3 Col 4 Col 5.... Col X

Id Name DOB City State

Desired Results:

ColumnNameTBD:

Id Name DOB City State

Thank You!

Tim

  • 2
    I think you may need dynamic SQL to handle the case where you don't know the names of the columns beforehand. See, for instance, the answer to [this question](http://stackoverflow.com/questions/11985796/pivot-dynamic-columns-no-aggregation). – Joe Farrell May 29 '15 at 17:07
  • 1
    That is because a dynamic number of columns violates everything about relational tables. I am guessing this "table" is the result of a dynamic pivot? What is not at all clear is what you are really trying to do. You listed 5 example columns with the same 5 columns in your output. Try to explain more clearly what is going on and we can find a way to help. – Sean Lange May 29 '15 at 17:20
  • 1
    Yes to Joe and Sean: @TimWiley, you'll probably need dynamic SQL (not necessarily a problem) and can you provide a more detailed example of what you want, including data. – Rory May 29 '15 at 17:40
  • We get a mangled excel spreadsheet and structured data doesn't begin until somewhere in the sheet that changes...There are 4 columns that are always there... the rest are variable(1 to 100).... So i import the excel spreadsheet raw into a SQL Server table and then i can locate the record descriptor row and anything below that is the structured data... We don't have control of the export format and i don't want to macro this in excel to clean it up as i want it fully automated... i just about have it working with Cross Apply, XML Path and dynamic sql as suggested. –  May 29 '15 at 18:28
  • Guys forgive me on Stack Etiquette as i wish i could give all of you the credit on the answer... –  May 29 '15 at 18:54

2 Answers2

0

After feedback from to the question i was able to piece together the solution. I now have a way to physically instanciate structure from dynamic/inconsistent structure automatically. 

    DECLARE @sColumnNames As Varchar(5000)
    DECLARE @sSQL  AS  Varchar(5000);

    --Flatten List of Column Names
     Select @sColumnNames = ' ' + (
                 Select '(' + COLUMN_NAME + '),' As 'data()'
                  From  SMAR_STG.INFORMATION_SCHEMA.COLUMNS  With (NoLock) 
                 where TABLE_NAME = 'V_ETL_CTX_COREM_EXCEL_DTL_COLUMNS'
                   For XML PATH('')
            )  + ' '

    --Get Rid or trailing Comma
    Set @sColumnNames = substring(@Output,1,len(@Output) -1) 

    -- Assemble SQL Statement
    Set @sSQL = 'SELECT Upivot AS X
                FROM   SMAR_STG.DBO.V_ETL_CTX_COREM_EXCEL_DTL_COLUMNS
                       CROSS apply (VALUES '
    Set @sSql = @sSQL + @sColumnNames
    Set @sSQL = @sSQL + ') cs (upivot) '

    -- Execute
    EXEC (@sSql)
-2

This item was already resolved here

As you will see from the article from the link, you will need to use COALESCE function to achive this

Community
  • 1
  • 1
Yuri
  • 2,820
  • 4
  • 28
  • 40