CODEWITHSUNDEEP
sql-serverpivot
Dan
Dan

Reputation: 45752

PIVOT in SQL Server with no aggregate function

I have a table like this (lets call it T1):

   Date    | Category | Data
-------------------------------
2014-01-01 | 1        | 1.0
2014-01-01 | 2        | 2.0
2014-01-01 | 3        | 3.0
2014-01-02 | 1        | 4.0
2014-01-02 | 2        | 5.0
2014-01-02 | 3        | 6.0

Note that Data are floating point numbers, not sequential, I just made them that way so it's easier to see where they land up in the result. There is also a table translating the Category numbers to names (lets say (T2):

Category | Name
----------------
1        | A
2        | B
3        | C

And I would like a query that could return:

   Date    |   B   |   C   
---------------------------
2014-01-01 |  2.0  |  3.0  
2014-01-02 |  5.0  |  6.0

I thought about trying with PIVOT which I haven't used before, however, I can't find a way to do it without using a aggregation function. But then I though that since I only actually have a single row per Date / Category combo using an aggregation like AVG should work. This is my try based on these docs:

SELECT [Date],
       [2] as B,
       [3] as C
FROM (SELECT * FROM T1 WHERE Category >= 2) AS SourceTable
PIVOT
(
    AVG(Data)
    FOR Category IN ([2], [3])
) AS PivotTable;

Which gets kind of close:

   Date    |   B   |   C   
---------------------------
2014-01-01 |  2.0  |  NULL  
2014-01-01 |  NULL |  3.0
2014-01-02 |  5.0  |  NULL
2014-01-02 |  NULL |  6.0

But how do I get rid of the nulls and get all the same dates to be on the same row?

Upvotes: 1

Views: 2348

Answers (1)

podiluska
podiluska

Reputation: 51494

If you only have one value for each result, you can use any of the aggregate functions - eg: MAX

select * from 
    (select t.date, t.data,c.name from t1 t inner join category c on t.category = c.category) s
pivot (max(data) for name in ([b],[c])) p

The first part is the source data

select t.date, t.data,c.name from t1 t inner join category c on t.category = c.category

Then the pivot rearranges it, creating columns for the in values from the name column, and using the aggregate (max(data)) for the value in that column, which should give the desired results.

If you're getting nulls, it's probably from using select * in your source query, rather than selecting the required fields - ie: replace 

SELECT * FROM T1 WHERE Category >= 2

with 

select category, data FROM T1 WHERE Category >= 2

Upvotes: 1

Related Questions