CODEWITHSUNDEEP
sqlsql-servert-sql
user2689893
user2689893

Reputation: 19

How to convert rows to columns?

I have a table like this:

Name Value
toyota yellow
nissan blue
toyota red
nissan black

I want to convert the table to this:

toyota nissan
yellow blue
red black

How can I do this?

I tried using this command:

SELECT * (CASE Name WHEN 'toyota' THEN Value END) toyota,
         (CASE Name WHEN 'nissan' THEN Value END) nissan
FROM testTable

But that results in a table like this:

toyota nissan
yellow NULL
NULL blue
red NULL
NULL black

Help? Thanks

Upvotes: 1

Views: 95

Answers (3)

i-one
i-one

Reputation: 5120

Using pivot is possible also:

declare @table table (Name varchar(50), Value varchar(50))
insert into @table values ('toyota', 'yellow'), ('nissan', 'blue'),
    ('toyota', 'red'), ('nissan', 'black')

;with t as (
    select *, rn = row_number() over (partition by Name order by Value)
    from @table
)
select Toyota, Nissan
from t
    pivot (max(Value) for Name in ([Toyota],[Nissan])) p

Upvotes: 2

podiluska
podiluska

Reputation: 51514

Using a full outer join

select toyota, nissan from 
(select value toyota, ROW_NUMBER() over (order by value desc) r 
      from testtable 
      where name = 'toyota') t
    full outer join
(select value nissan, ROW_NUMBER() over (order by value desc) r 
      from testtable 
      where name = 'nissan') n
    on t.r =n.r

Upvotes: 2

Jonny
Jonny

Reputation: 2519

Let me ask a question and hopefully it will illustrate why it isn't doing what you expected.

You want 

toyota    nissan
----------------
yellow    blue
red       black

but equally you could have

toyota    nissan
----------------
yellow    black
red       blue

How does the database decide that yellow and blue go in the same line? There is nothing in the original table to tie the two rows together.

The database doesn't know which of black or blue to associate yellow with, so it doesn't associate it with either.

Upvotes: 2

Related Questions