CODEWITHSUNDEEP
sqlsql-server
Jackson Tale
Jackson Tale

Reputation: 25812

sql - How to select so that the row can be column?

I have a table like this:

   Item      Name             Name_location   Price

    1      item1_london        london         10
    1      item1_beijing       bejing         10
    2      item2_london        london         20
    2      item2_beijing       bejing         20

Basically, this table means I have lots of items, each item will have a different name in different location (two locations: london and beijing).

How can I query so that I can get the table like this:

  Item     london             bejing          Price
    1      item1_london       item1_beijing    10
    2      item2_london       item2_beijing    20

I am using MS SQL Server.

Edit: updated the two tables, add another column

Upvotes: 0

Views: 92

Answers (4)

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT DISTINCT o.Item, l.itemName as London, b.ItemName as Beijing
FROM myTable o inner join myTable l ON o.item = l.item
INNER JOIN myTable b ON o.item = b.item
WHERE l.location = 'london' AND b.location = 'beijing'

SQLFiddle link: http://sqlfiddle.com/#!3/93c2a/8

Upvotes: 0

Taryn
Taryn

Reputation: 247680

If you only have two locations, then you can use a CASE statement with aggregate:

CASE with Aggregate:

select item,
  max(case when Name_location = 'london' then name end) london,
  max(case when Name_location = 'bejing' then name end) bejing,
  sum(price) price
from yourtable
group by item

See SQL Fiddle with Demo

or 

select item,
  max(case when Name_location = 'london' then name end) london,
  max(case when Name_location = 'bejing' then name end) bejing,
  price
from yourtable
group by item, price

Using the PIVOT function, there are two ways either static/dynamic:

Static PIVOT

select item, [london], [bejing], price
from 
(
  select item, name, name_location, price
  from yourtable
) x
pivot
(
  max(name)
  for name_location in ([london], [bejing])
) p

See SQL Fiddle with Demo

While the static PIVOT versions will work great if you have an known number of values, if you have an unknown number then you can use dynamic sql:

Dynamic Pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name_location) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item,' + @cols + ', price from 
             (
                select item, name, name_location, price
                from yourtable
            ) x
            pivot 
            (
                max(name)
                for name_location in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Upvotes: 4

Larry
Larry

Reputation: 2252

enter image description here

select item,london,beijing
from t1
pivot(max(Name) for Name_location in (london, beijing)) pvt

enter image description here

select item,london,beijing,price
from t1
pivot(max(Name) for Name_location in (london, beijing)) pvt

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Using a pivot

select * 
from YourTable src
pivot (max(name) for name_location in ([london], [beijing]) ) p

Upvotes: 1

Related Questions