I have a dataset, that is derived from a query. The easiest way to explain this issue is to show you what the data set looks like.

Let's say I create this Table, and insert the data.

Create table #tempTable2 (Locaiton1 decimal(10,2), Location2 Decimal(10,2), Location3 decimal(10,2), Location4 decimal(10,2),
Location5 Decimal(10,2))
Insert into #temptable2 Values ('22.4','33.4', '12.4', '95.94','23.40')
select * 
from #tempTable2

When You select the data, your output is:

Location1 |  Location2 | Location3 |  Location4| Location5
   22.4   |  33.4      | 12.4      |  95.94    | 23.40

What I'd like to do is display the data like this:

Location  | Amount
Location1 | 22.4
Location2 | 33.4
Location3 | 12.4
Location4 | 95.94
Location5 | 23.40

How can I accomplish this in SQL Server?

CodePudding user response：

Fixing the typo in the #tempTable2 definition, using CROSS APPLY prevents you from having to scan the table N times, once for each Location column:

SELECT y.Location, y.Amount
FROM #tempTable2 AS t2
CROSS APPLY (VALUES
  ('Location1', Location1),
  ('Location2', Location2),
  ('Location3', Location3),
  ('Location4', Location4),
  ('Location5', Location5)
) AS y (Location, Amount);

Output:

• Example db<>fiddle

CodePudding user response：

I have to say that the structure of your table is terrible and that it should be structured like your desired results right from the get-go with two columns. I also understand that it's likely this table already exists and you have no control over the schema.

To get to your desired results, you can use a UNION:

SELECT 'Location1' as Location, Location1 as Amount FROM yourtable
UNION ALL
SELECT 'Location2' as Location, Location2 as Amount FROM yourtable
UNION ALL
SELECT 'Location3' as Location, Location3 as Amount FROM yourtable
UNION ALL
SELECT 'Location4' as Location, Location4 as Amount FROM yourtable
UNION ALL
SELECT 'Location5' as Location, Location5 as Amount FROM yourtable