How Do I Convert SQL Columns to Rows?

Question

I've been looking at how to do this all morning, but I'm in dire need of help. I'm an SQL newb, I'm looking to convert my columns to rows right now. Currently all of my columns go horizontally:

[ ][Group] [Location] [Item] [Model Number]...[Data Update/BU]
[1][Admin] [Noc]      [PC]   [Optiplex 760]   [N/A]

The idea of this database is that I would like to query a serial number, and get all the information related to it. The query I execute for this is:

SELECT * FROM Table
WHERE Serial = '1234567890'

Ideally, I would like to be able to view this data in a more vertical sense like below when I execute that query:

[ ] [COL]                     [DATA]
[1] [Group]                   [Admin]
[2] [Location]                [NOC]
[3] [Item]                    [Desktop PC]
[4] [Model Number]            [Optiplex 760]
[5] [Role]                    [Server]
[6] [User Name]               [User]
[7] [Serial]                  [1234567890]
[8] [Workgroup/Domain]        [Workgroup]
[9] [IP Address]              [192.192.192.192]
[10] [Computer Name]          [User-PC]
[11] [Operating System]       [Windows]
[12] [32/64-Bit]              [64-Bit]
[13] [HDD]                    [1TB]
[14] [RAM]                    [8GB]
[15] [CPU]                    [i5]
[16] [Toner/Ink]              [N/A]
[17] [Notable Software]       [Office 2003]
[18] [Notes]                  [N/A]
[19] [Cleaning Summary]       [Cleaned 5/6, updated, blown out, etc...]
[20] [Reboot]                 [N/A]
[21] [Data Update/BU]         [N/A]

Any help would be appreciated...Thanks!

During your research, did you encounter the term/function `PIVOT`? — HoneyBadger, May 06 '16 at 14:48
I did, but I'm not quite wrapping my head around it. The examples I'm seeing are quite different haha. — DJH Computers, May 06 '16 at 14:51
Understandable, it isn't an easy function to visualize. I would advise to practice with a small query, with just a couple of columns. With 21 columns, get ready for a lot of typing :P (I'm sorry, but I don't have the time to assist any further) — HoneyBadger, May 06 '16 at 14:54
You should also look into the EAV database model and why it's generally a bad idea. — Tom H, May 06 '16 at 14:56
Possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — devlin carnate, May 06 '16 at 14:58
@DJHComputers - just because it's difficult to understand at first, doesn't mean it is incorrect. PIVOT / UNPIVOT are the way to do this. https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — EastOfJupiter, May 06 '16 at 15:05
which dbms are you working in? I know you can use UNPIVOT to do this in oracle and sql server — msheikh25, May 06 '16 at 15:38

score 1 · Answer 1 · edited Jul 25 '18 at 08:50

You can use UNPIVOT. But column types have to be same for UNPIVOT to work so you would need to cast before you unpivot. Like this

SELECT COL, DATA
FROM 
(
    SELECT cast(Column1 as nvarchar(100)) as  Column1,
        cast(Column2 as nvarchar(100)) as  Column2,
        cast(Column3 as nvarchar(100)) as  Column3,
    FROM Table
    WHERE Serial = '1234567890'
) tbl
UNPIVOT
(
    DATA FOR COL IN (Column1,Column2,Column3)
) as UnPvt

score 0 · Answer 2 · edited Jul 25 '18 at 08:52

You actually want an UNPIVOT for this:

SELECT COL, DATA
FROM ( SELECT * FROM Inventory ) inv
UNPIVOT
(
    DATA FOR COL IN ([Group], [Location], [Item], [Model Number]
                , [Role], [User Name], [Serial], [Workgroup/Domain]
                , [IP Address], [Computer Name], [Operating System]
                , [32/64-Bit], [HDD], [RAM], [CPU], [Toner/Ink], [Notable Software]
                , [Notes], [Cleaning Summary], [Reboot], [Data Update/BU]
    )
) as UnPvt

Depending on how often you need this/who will use it, you could much more easily accomplish the same thing in Excel using Copy/Paste or a Pivot Table.

score 0 · Answer 3 · edited Jul 25 '18 at 08:51

UNPIVOT in oracle:

WITH test_data(serial, grp,  loc, item, mdl)
    AS (select 1, 'Admin', 'Noc', 'PC', 'Optiplex 760' from dual)

select COL, DATA
FROM test_data
unpivot
(
    DATA for COL in (grp, loc, item, mdl) 
);

UNPIVOT in SQL Server is a bit more tricky because the list of columns you unpivot need to be the same type. So easiest way is to convert them all first so a common type (there maybe an easier/cleaner way to convert but here is what I came up with):

demo it here: http://rextester.com/PGETS7930

WITH test_data(grp, loc, item, mod)
    AS (select 'Admin', 'Noc', 'PC', 'Optiplex 760')

select COL, DATA FROM (
    select 
      cast(grp as varchar(15)) grp,
      cast(loc as varchar(15)) loc,
      cast(item as varchar(15)) item,
      cast(mod as varchar(15)) mod
    from test_data
) t

UNPIVOT 
( DATA 
  for COL in (grp, loc, item, mod)
) as unpvt
;

How Do I Convert SQL Columns to Rows?

3 Answers3