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?

Answer 1

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)

Answer 2

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)