or
JustinDoesWork imported from SE
sql-server sql-server-2012
Example Schema:

    CREATE TABLE [dbo].[Base](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Option1ID] [int] NULL,
    [Option2ID] [int] NULL,
    [Option3ID] [int] NULL,
    [Option1Name] [varchar] NULL,
    [Option2Name] [varchar] NULL,
    [Option3Name] [varchar] NULL,
    [Option1LName] [varchar] NULL,
    [Option2LName] [varchar] NULL,
    [Option3LName] [varchar] NULL,)

Is there a way to get results that show up like:

    ID | OptionID | OptionName | OptionLName

I have tried to achieve this using UNION ALL but this means going over the same row 3 times in my example, in my real problem I have to do it 10 times. I cannot normalize the table due to legacy code. Is there a way to only go over the Base row once?
Top Answer
Martin Smith
You can use `CROSS APPLY ... VALUES` to `UNPIVOT` multiple columns

    SELECT ID,
           OptionID,
           OptionName,
           OptionLName
    FROM   [dbo].[Base]
           CROSS APPLY (VALUES([Option1ID], [Option1Name], [Option1LName]),
                              ([Option2ID], [Option2Name], [Option2LName]),
                              ([Option3ID], [Option3Name], [Option3LName])) 
                         V( OptionID, OptionName, OptionLName) 

The execution plan for this has one scan of `Base`. The plan is in fact the same as for the 2005 compatible rewrite that uses `UNION ALL`


    SELECT ID,
           OptionID,
           OptionName,
           OptionLName
    FROM   [dbo].[Base]
           CROSS APPLY (SELECT [Option1ID], [Option1Name], [Option1LName] UNION ALL
                        SELECT [Option2ID], [Option2Name], [Option2LName] UNION ALL
                        SELECT [Option3ID], [Option3Name], [Option3LName]) 
                         V( OptionID, OptionName, OptionLName)  

But I presume the `UNION ALL` you were trying to avoid was the multiple scans of 


    SELECT ID,
           [Option1ID],
           [Option1Name],
           [Option1LName]
    FROM   [dbo].[Base]
    UNION ALL
    SELECT ID,
           [Option2ID],
           [Option2Name],
           [Option2LName]
    FROM   [dbo].[Base]
    UNION ALL
    SELECT ID,
           [Option3ID],
           [Option3Name],
           [Option3LName]
    FROM   [dbo].[Base] 
Is there a better option than Union All for multiple selects from the same row?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.