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?
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]
