Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How do I convert column data into row data? RE: How do I convert column data into row data?

  • September 12, 2013 at 1:19 am

    #1649592

    Erland Sommarskog (9/11/2013)

    ChrisM@Work (9/4/2013)

    Or CROSS APPLY VALUES

    Somewhat odd to use CROSS APPLY here. The normal is CROSS JOIN. Of course since there is no correlation on the right side, the CROSS APPLU is effectively a CROSS JOIN, but nevertheless.

    There are similarities and differences to CROSS JOIN. All rows from the RHS are matched to all rows on the LHS, for instance. However, whilst the VALUES clause of a CAV block introduces a table source such as CROSS JOIN requires, the values are taken from the "current row" on the LHS, from any table source in scope.

    (CROSS) APPLY without introducing a table source effectively translates to "calculate new columns" using the current row(s). VALUES expands on this functionality allowing you to create new rows as well. Correlation is moot - you play with the data already in scope.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden