Converting one column to column headers

Question

I'm still trying to wrap my head around Pivot and Unpivot and the like, but I can't for the life of me figure out how to do something in T-SQL that I'm sure is crazy simple. So I'm hoping some of you SSMS/T-SQL gurus can help me out here.

What I need to do is to turn something like this:

Salesman    Number of Clients   # of Sales Last Year    # of Projected Sales
----------------------------------------------------------------------------
Rob              44                    200                    150
Bill             28                    120                    100           
Thomas           60                    300                    320

Into something like this:

                       Rob   Bill   Thomas
Number of Clients      44    28     60
# of Sales Last Year   200   120    300
# of Projected Sales   150   100    320

All of the pivot and unpivot examples I've seen seem to only work if you're aggregating values with SUM and the like. I don't need to aggregate anything. I just need to shift one of the columns into column headers, basically.

I have seen some other examples here. But they all either use aggregation or single columns of info. None does the same thing that I'm looking to do here. I just need some simple code to do what should be a very simple task. If TSQL simply can't do this without some complicated kludgy work-around, then a simple "TSQL can't do that" will work too.

Possible duplicate of [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Brad Colacino, May 09 '18 at 20:13

DxTx · Answer 1 · 2018-05-10T01:01:58.743

To get the output you need, you can use both PIVOT and UNPIVOT clauses...

SELECT * 
FROM   (SELECT * 
        FROM   tablename 
               UNPIVOT(vls 
                      FOR vlsnames IN ([NumberofClients], 
                                       [ofSalesLastYear], 
                                       [ofProjectedSales] )) AS unpvt) tmp 
       PIVOT( Max(vls) 
            FOR salesman IN ([Rob], [Bill], [Thomas]) ) AS pvt;

Output (step by step)

+----------+-----------------+-----------------+------------------+
| Salesman | NumberofClients | ofSalesLastYear | ofProjectedSales |
+----------+-----------------+-----------------+------------------+
| Rob      |              44 |             200 |              150 |
| Bill     |              28 |             120 |              100 |
| Thomas   |              60 |             300 |              320 |
+----------+-----------------+-----------------+------------------+

UNPIVOT clause 
+----------+-----+------------------+   
| Salesman | vls |     vlsnames     |      ;;;;;
+----------+-----+------------------+      ;;;;;
| Rob      |  44 | NumberofClients  |      ;;;;;
| Rob      | 200 | ofSalesLastYear  |    ..;;;;;..
| Rob      | 150 | ofProjectedSales |     ':::::'
| Bill     |  28 | NumberofClients  |       ':`
| Bill     | 120 | ofSalesLastYear  |
| Bill     | 100 | ofProjectedSales |
| Thomas   |  60 | NumberofClients  |
| Thomas   | 300 | ofSalesLastYear  |
| Thomas   | 320 | ofProjectedSales |
+----------+-----+------------------+

PIVOT clause - PIVOT(UNPIVOT)  
+------------------+-----+------+--------+
|     vlsNames     | Rob | Bill | Thomas |
+------------------+-----+------+--------+
| NumberofClients  |  44 |   28 |     60 |
| ofProjectedSales | 150 |  100 |    320 |
| ofSalesLastYear  | 200 |  120 |    300 |
+------------------+-----+------+--------+

Online Demo: http://www.sqlfiddle.com/#!18/0df49/9/2

^{Reference:

https://www.techonthenet.com/sql_server/pivot.php

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017}

Thanks! That's exactly what I was looking for! – Jim Sloun May 09 '18 at 20:52 — Jim Sloun, May 09 '18 at 20:52

score 1 · Answer 2 · answered May 09 '18 at 20:32

I find it a bit easier if you change the format of your dataset.

declare @example table (
    ExampleID       int identity(1,1) not null primary key clustered
,   SalesPerson     nvarchar(255) not null
,   AmountOfClient  int not null
,   SalesLastYear   int not null
,   ProjectedSales  int not null
);

insert into @example (SalesPerson, AmountOfClient, SalesLastYear, ProjectedSales)
select 'Rob', 44, 200, 150 union all
select 'Bill', 28, 120, 100 union all  
select 'Thomas', 60, 300, 320;

;with cte as (
select SalesPerson
     , AmountOfClient as Metric
     , 'Clients' as [Type]
  from @example

union all

select SalesPerson
     , SalesLastYear
     , 'LastYear' as [Type]
  from @example

union all

select SalesPerson
     , ProjectedSales
     , 'Projected' as [Type]
  from @example
    )

 --select * from cte
select [Type]
     , [Rob]
     , [Bill]
     , [Thomas]
      from
        (
         select SalesPerson
              , metric
              , [type]
           from cte
           ) source
  pivot
        (
            max(Metric) 
                for SalesPerson in ([Rob], [Bill], [Thomas])
        ) as pvt;

Result Set

Type        Rob   Bill  Thomas
Clients     44    28    60
LastYear    200   120   300
Projected   150   100   320

Converting one column to column headers

2 Answers2