CODEWITHSUNDEEP
sqlsql-servert-sql
jeremy5
jeremy5

Reputation: 39

Using pivot in one select statement

I have table with columns:

[Date]       [Name]     [Result]
2010-01-01   Name01     11
2010-01-01   Name02     22
2010-07-04   Name01     32
2010-07-04   Name02     54

I'm working on SELECT statement which give me a result like this:

[Name]  [2010-01-01] [2010-07-04]
Name01       11           32
Name02       22           54

I've tried PIVOT function, but it's not working.

SELECT * FROM (

SELECT Date,Name,Result
FROM tbl_Results
WHERE (..conditions..)
)
PIVOT (
Result FOR Date in
([1],[2],[3],[2],[5])
)

Error message

Incorrect syntax near the keyword 'PIVOT'.

It's SQL server 2005, I'm not sure if PIVOT is available?

Maybe use any other function ?

Also I don't know how to set column name as date from select.

UPDATE

Thank you for all answers. The problem is that I don't know how many columns will be in the result. So I can't use fixed dates values like '2010-07-04'. The date is used "on the fly" in statement from table.

Upvotes: 1

Views: 270

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

I see in your update that you will need DYNAMIC SQL. Here is a stored procedure that will pivot virtually any table, or ad-hoc query. There is also the added utility of creating additional row aggregations

Example

Exec [prc-Pivot] '#YourTable','Date','sum([Result])[]','Name','count(*)[Records],min(Result)[MinValue],max(Result)[MaxValue]'

Or As Simple As 

Exec [prc-Pivot] '#YourTable','Date','sum([Result])[]','Name',null

Returns

enter image description here

The Stored Procedure

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

Set NoCount On
Set Ansi_Warnings Off

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)


Set NoCount Off
Set Ansi_Warnings on

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT *  FROM
(
    SELECT A.Name,A.Date,A.Result 
    FROM your_table A
    --WHERE A.Name = 'Name01'
) A
PIVOT
(
    SUM(Result)
    FOR Date IN ([2010-01-01],[2010-07-04])
)PVT

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Just use conditional aggregation:

select name,
       max(case when date = '2010-01-01' then result end) as result_20100101,
       max(case when date = '2010-07-04' then result end) as result_20100704
from tbl_results
where . . . 
group by name;

Then upgrade your system to a supported database.

Upvotes: 2

Related Questions