How to create a pivot query in sql server without aggregate function

Question

I am using MS SQL SERVER 2008 and I have following data:

select * from account;

| PERIOD | ACCOUNT | VALUE |
----------------------------
|   2000 |   Asset |   205 |
|   2000 |  Equity |   365 |
|   2000 |  Profit |   524 |
|   2001 |   Asset |   142 |
|   2001 |  Equity |   214 |
|   2001 |  Profit |   421 |
|   2002 |   Asset |   421 |
|   2002 |  Equity |   163 |
|   2002 |  Profit |   325 |

I want to make them to be this:

| ACCOUNT | 2000 | 2001 | 2002 |
--------------------------------
|   Asset |  205 |  142 |  421 |
|  Equity |  365 |  214 |  163 |
|  Profit |  524 |  421 |  325 |

I've tried to query use pivot query but the value have to use aggregate function and the result is not appropriate. what should I do?

If there is only a single value for each combination of row/column for the pivot, then there is no *harm* in using any aggregate which returns its input if given a single input, e.g. `SUM`, `MAX`, `MIN`. If there *isn't* a single value for each combination, then you need to give us (and ultimately, SQL Server) a rule for figuring out what the result should be. — Damien_The_Unbeliever, Jan 31 '13 at 09:01
possible duplicate of [Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)](http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query) — RichardTheKiwi, May 03 '13 at 11:20

score 44 · Answer 1 · answered Jan 31 '13 at 04:18

44

SELECT *
FROM
(
SELECT [Period], [Account], [Value]
FROM TableName
) AS source
PIVOT
(
    MAX([Value])
    FOR [Period] IN ([2000], [2001], [2002])
) as pvt

Another way,

SELECT ACCOUNT,
      MAX(CASE WHEN Period = '2000' THEN Value ELSE NULL END) [2000],
      MAX(CASE WHEN Period = '2001' THEN Value ELSE NULL END) [2001],
      MAX(CASE WHEN Period = '2002' THEN Value ELSE NULL END) [2002]
FROM tableName
GROUP BY Account

SQLFiddle Demo (both queries)

answered Jan 31 '13 at 04:18

John Woo

258,903
69
498
492

The period is dynamic, how to create pivot without aggregate function, because i need to show all value – Fitrie Adytia Wibawa Jan 31 '13 at 04:31

score 7 · Answer 2 · answered Jan 31 '13 at 04:47

Check this out as well: using xml path and pivot

SQLFIDDLE DEMO

| ACCOUNT | 2000 | 2001 | 2002 |
--------------------------------
|   Asset |  205 |  142 |  421 |
|  Equity |  365 |  214 |  163 |
|  Profit |  524 |  421 |  325 |

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.period) 
            FROM demo c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT account, ' + @cols + ' from 
            (
                select account
                    , value
                    , period
                from demo
           ) x
            pivot 
            (
                 max(value)
                for period in (' + @cols + ')
            ) p '


execute(@query)

@FitrieAdytiaWibawa here is another solution and it matches your expected output :) — bonCodigo, Jan 31 '13 at 04:49
Excellent answer. Helped me with an SEDE query: http://data.stackexchange.com/codereview/query/167924/top-x-user-reputation?Days=60&TopX=15#graph Thanks. +1 applied — rolfl, Feb 15 '14 at 23:39

How to create a pivot query in sql server without aggregate function

2 Answers2

Linked