CODEWITHSUNDEEP
sqlsql-servert-sql
NSaid
NSaid

Reputation: 751

SQL - transpose rows to columns

I am trying to achieve the following:

Given: 

Name | Color   | Value |
------------------------
John |  Red    |   2   |
------------------------
John |  Blue   |   5   |
------------------------
John | Yellow  |   10  | 
------------------------

Expected:

Name |  Red  |  Blue  |  Yellow  |
----------------------------------
John |   2   |    5   |    10    |
----------------------------------

Essentially what I am hoping to do is make the color column individual colums with their value as the row value. There is a catch though, you can only use the following SQL functions:

Aggregates

Avg(), Count(), Max(), Min(), Sum(), Round()

Arithmetic Operators

Comparison Operators

= (equals), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (not equal to), != (not equal to), !< (not less than), !> (not greater than)

Logical Operators

And, Or, Not, Between, In, Like, Any, All, Some, Exists

Functions

Case, Cast, Convert, Current_timestamp, DateAdd, DateDiff, DateName, DatePart, GetDate(), IsDate, Left, Len, Lower, Ltrim, Replace, Replicate, Reverse, Right, Rtrim, Space, Str, Substring, SUBSTRING ( expression, start , length), Sysdatetime, Upper

I have been able to get it to show the color as the column header and return its value. However this results in additional color values being returned too like so:

Name |   Red   |
----------------
John |    2    |
----------------
John |         |
----------------
John |         |
----------------

I know that the other color values are occupying the other rows because I added an additional column with the color name. The formula I used is as follows:

CASE Color WHEN 'Red' THEN Value END

I know I am missing a constraint but I don't know what I am missing. Any help would be appreciated.

Upvotes: 0

Views: 382

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

Late update. If 2012+, perhaps you can use the window function.

There is no GROUP BY, but there is an DISTINCT.

Example

Select Distinct
       Name
      ,Red   = sum(case when Color='Red'    then Value end) over (Partition By Name)
      ,Blue  = sum(case when Color='Blue'   then Value end) over (Partition By Name)
      ,Yellow= sum(case when Color='Yellow' then Value end) over (Partition By Name)
 From  @YourTable

Upvotes: 1

William Jones
William Jones

Reputation: 204

How about "correlated subqueries"?

SELECT ot.NAME, 
(SELECT SUM(t1.RED) FROM xTable as t1 where t1.NAME = ot.NAME) as RED,
(SELECT SUM(t1.BLUE) FROM xTable as t1 where t1.NAME = ot.NAME) as BLUE,
(SELECT SUM(t1.YELLOW) FROM xTable as t1 where t1.NAME = ot.NAME) as YELLOW
FROM xTable as ot

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

If I understand, you're just missing the conditional aggregation and Group By

Example

Select Name
      ,Red   = sum(case when Color='Red'    then Value end)
      ,Blue  = sum(case when Color='Blue'   then Value end)
      ,Yellow= sum(case when Color='Yellow' then Value end)
 From  YourTable
 Group By Name

Returns

Name    Red  Blue   Yellow
John    2    5      10

Upvotes: 4

Related Questions