SQL Rows to Columns with only two columns

Question

This question has been asked for many times but I can not relate any of the answers I have read so far to my exact question. Let us say that my original table has 2 columns.

Brand      Type
Mercedes   Sedan
BMW        Sedan
Mercedes   SUV
Jeep       SUV
VW         Sedan
VW         Small Family

I want to format the original table like the following

[Sedan]    [SUV]       [Small Family]
BMW        Mercedes    VW    
Mercedes   Jeep        NULL   
VW         NULL        NULL

Is this possible with any sort of SQL in any sort of environment (i.e. SQL server, Oracle, DB2), with or without PIVOT function?

Regardless the method you use to pivot (pivot table operator, case statement) There is no problem in pivoting the rows into columns, but the problem is that you need a third column to apply the pivot, so you need to generate a row number for example so that you can get only three rows and eliminate the null values and that is different from RDBMS to another. — Mahmoud Gamal, Oct 08 '15 at 09:44

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

To answer your question directly, the easiest way to do that is to use PIVOT. , If you want to make the rows into columnar form, or columns to form as rows, the solution is only PIVOT.

SQL Server has a PIVOT function that you can use to achieve that, this link can help you: Convert Rows to columns using 'Pivot' in SQL Server

EDIT:

PIVOT sample:

    SELECT [Sedan], [SUV], [Small Familiy] FROM
        (
            SELECT ROW_NUMBER()
            OVER (PARTITION BY [TYPE] ORDER BY Brand) RowNum, Brand, [Type] FROM tblCar
        ) AS src
        PIVOT 
        (
            MIN(Brand) FOR [Type] IN ([Sedan], [SUV], [Small Familiy])
        ) AS pvt

SQL Fiddle sample for PIVOT

Hope this helps.

edited May 23 '17 at 12:29

Community

1
1

answered Oct 08 '15 at 09:42

japzdivino

1,736
3
17
25

***`PIVOT` is not the only way***, for example you can pivot using `case` statement. – Mahmoud Gamal Oct 08 '15 at 09:46
All the examples I've seen so for on PIVOT tables use a column like an ID or whatnot and convert rows to columns for each ID. For my case, I don't have such a column for a reference.. Do you have any examples for my current case? – lollified Oct 08 '15 at 09:47
@MahmoudGamal , my bad, what i mean is **easiest** , post already modified. Thanks :) – japzdivino Oct 08 '15 at 09:48
@lollified you can use the `MAX or MIN` function of SQL for strings if you dont have `ID` – japzdivino Oct 08 '15 at 10:20

score 0 · Answer 2 · edited Oct 08 '15 at 13:15

0

Select 
    (CASE Type = 'Sedan' 
        then Brand 
        else null 
    end) Sedan, 
    (CASE Type = 'SUV' 
        then Brand 
        else null 
    end) SUV, 
    (CASE Type = 'Small Family' 
        then Brand 
        else null 
    end) SmallFamily 
FROM table

edited Oct 08 '15 at 13:15

Kritner

13,557
10
46
72

answered Oct 08 '15 at 09:52

rgstamayo

163
12

score 0 · Answer 3 · answered Oct 08 '15 at 10:03

DECLARE @Table1 TABLE 
    (Brand varchar(8), Type varchar(12))
;

INSERT INTO @Table1
    (Brand, Type)
VALUES
    ('Mercedes', 'Sedan'),
    ('BMW', 'Sedan'),
    ('Mercedes', 'SUV'),
    ('Jeep', 'SUV'),
    ('VW', 'Sedan'),
    ('VW', 'Small Family')
;


Select [Sedan],[SUV],[Small Family] FROM (
select ROW_NUMBER()OVER(PARTITION BY TYPE ORDER BY TYPE)RN,Brand, Type from @Table1)T
PIVOT (
MAX(brand) FOR TYPE IN ([Sedan],[SUV],[Small Family]))P

for clarity, this works perfectly. thank you so much. – lollified Oct 08 '15 at 10:17 — lollified, Oct 08 '15 at 10:17

score 0 · Answer 4 · answered Oct 08 '15 at 10:06

0

Try following:

select Sedan, SUV, [Small Family]
from
(
  select ROW_NUMBER()OVER(PARTITION BY TYPE ORDER BY TYPE)RN,Brand, Type from Cars)Cars
pivot
(
  max(Brand)
  for [Type] in (Sedan, SUV, [Small Family])
) piv;

answered Oct 08 '15 at 10:06

Shaikh Farooque

2,620
1
19
33

SQL Rows to Columns with only two columns

4 Answers4