CODEWITHSUNDEEP
sqlsql-servert-sql
yvz
yvz

Reputation: 61

Swapping rows and columns in SQL Server

I want to make city1, city2, city3 lines as columns. Likewise, I want to display numerical values ​​horizontally.

I shared the original painting:

Original painting.

Table detail:

create table table_name
(
    id int identity,
    city nvarchar(10),
    The_number_of_customers int
)

This is the output I want:

city name 1 | city name 2 | city name 3 | city name 4 |
------------+-------------+-------------+-------------+
   60       |     75      |     150     |     125     |

Thank you.

Upvotes: 0

Views: 201

Answers (2)

Tyron78
Tyron78

Reputation: 4187

Sounds like a good case vor pivot:

DECLARE @table_name table (
id int,
city nvarchar(1060),
The_number_of_customers int
)

INSERT INTO @table_name VALUES
(1, 'city name 1', 60)
,(2, 'city name 2', 75)
,(3, 'city name 3', 150)
,(4, 'city name 4', 125)

;WITH cte AS(
  SELECT city, The_number_of_customers
    FROM @table_name
)
SELECT *
  FROM cte t
PIVOT
(
 MAX(t.The_number_of_customers) FOR city IN ([city name 1], [city name 2], [city name 3], [city name 4])
) AS p

Upvotes: 1

Popeye
Popeye

Reputation: 35900

Use conditional aggregation as follows:

select max(case when city = 'city name1' then The_number_of_customers  end) as "city name 1",
       max(case when city = 'city name2' then The_number_of_customers  end) as "city name 2",
       max(case when city = 'city name3' then The_number_of_customers  end) as "city name 3",
       max(case when city = 'city name4' then The_number_of_customers  end) as "city name 4"
  from table_name

Upvotes: 0

Related Questions