Hi,

This tip is really a useful one.I am having clarification in this.In the example you provided under Products columns there were only two product values so its easy to use that in outer select for naming.My doubt is, suppose, if we are having more records in the column,how can we proceed?.Whether we need to proceed like the same way or is there any better option?.Could you please help me on this.

Thanks in advance,

Regards,

Swami

Hi Greg,

  This article is very useful for me,i have no words to expressed your article. Thanx a lot !!!

Hi Greg,

Thank you for this tutorial. have a question, how do place that to the page using PHP?

Thanks for this.

but i have an issue when the number of product is unknown and it can be any number.

say in this case if we have 100 products how can we get the product name in to columns dynamically  rather than hard coding the column name ?

can any one help me

@wajira - take a look at this article to see if this answers what you need to do: http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

Hi Greg

Thanks a lot

Hi Greg,

  This article is very useful for me. Thanks a lot.

Regards

MADRI PRATAP

This article was good and helped me. Thanks a lot.

Hi Greg,

This article is very useful for me.

Thanks.

This is my table………..

 I need result like below table…how to write query for getting this table.

@vanapandi - try this query.

SELECT paymentid,
SUM(case when receivedtype = 'dr' then amount
 when receivedtype = 'cr' then -amount end),
reason
FROM #temp a
GROUP BY paymentid, reason

Hi Greg, thanks for the examples, they were helpful.

I was wondering if there was an easy way to do a crosstab in SQL server if you don't know all of the possible values for the crosstab column headers, or if there are a large number of them?  For example, I have a list of people, and I want to show an aggregate value (let's say sales) for each person, for each month spanning several years.  Do I need to manually type out every month value as a column header, or is there a way to let SQL Server figure that out on its own?

Access makes this sort of thing very easy, unfortunately the database I'm working with exceeds the limits of what Access can handle.

Thanks.

@Chris F - take a look at this tip written by Aaron Bertrand:  http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

Thanks for your help and the quick reply.  This was exactly what I needed.

hi sir plz help for this problem

In my table the values are stored as
empid ename date status
————————————————
101 abc 01/02/2013 present
101 abc 02/02/2013 present
101 abc 03/02/2013 absent
102 xyz 03/02/2013 present
and so onnnn
101 abc 28/02/2013 present
102 xyz 28/02/2013 present

here i mention only one employe record.There is n number employes there my problem is
how to show table like this

empid ename 1 2 3 4 5 6 …….28
————————————————————————
101 abc present present absent…………. presnt
102 xyz present present present………..present

here 1 2 3 4 represent dates of the month.

plese kindly provide me the query to get like this.
in this above case empid is also varchar

thanks.

@nagaraj -- Try this.  Note that it will give NULL values for any day the employee is missing a record.  I'm sure there's a way to adjust for this but I don't have time at the moment to figure it out.

--Example table
--(I've changed your dates from DD/MM/YYYY to MM/DD/YYYY format since my version of SQL can't handle DD/MM/YYYY.)
create table Example (EmpID varchar(10), EName varchar(25), Date datetime, Status varchar(10));
insert into Example select '101', 'abc', '02/01/2013', 'present';
insert into Example select '101', 'abc', '02/02/2013', 'present';
insert into Example select '101', 'abc', '02/03/2013', 'absent';
insert into Example select '102', 'xyz', '02/03/2013', 'present';
insert into Example select '101', 'abc', '02/28/2013', 'present';
insert into Example select '102', 'xyz', '02/28/2013', 'present';

--Need a new column to hold just the day number, since that's what we're pivoting on.
alter table Example add DayNumber varchar(2);
update Example set DayNumber = cast(datepart(dd,Date) as varchar(2));

--Temp table for day numbers, because we need them as varchar but sorted as int.
select DISTINCT datepart(dd,Date) "DayNumber"
into #Days from Example order by 1;

--Build column list, get all crosstab column names.
declare @columns varchar(max);
set @columns = '';
select @columns = @columns + ', [' + cast(DayNumber as varchar(2)) +']' from #Days;

--Build SQL query.
declare @sql varchar(max);
set @sql = 'select EmpID, EName, '
+ stuff(@columns,1,2,'') + ' from (
    select EmpID, EName, DayNumber, Status from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ('
+ stuff(replace(@columns, ', PVT.[', ','), 1, 1, '')
+ ')
) as PVT order by EmpID;';
print @sql;
execute(@sql);

Hai sir ,

declare @columns varchar(max);
set @columns='';
select @columns=@columns+',['+cast(daynum as varchar(2))+']' from #days;

declare @sql varchar(max);
set @sql = 'select EmpId, Ename, '
+ stuff(@columns,1,2,'') + ' from (
    select EmpId, Ename, DayNumber, Status from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ('
+ stuff(replace(@columns, ', PVT.[', ','), 1, 1, '')
+ ')
) as PVT order by EmpId;';
print @sql;
execute(@sql)

While Executing the above query It gives the bellow ouput sir

------------------------------------------

select EmpId, Ename, 1],[2] from (
    select EmpId, Ename, DayNumber, Statuss from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ([1],[2])
) as PVT order by EmpId;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ']'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.

Please tell me the solution sir.

Thanks.

Can you post your SQL version information?  I created this on:

Microsoft SQL Server Management Studio    9.00.1399.00
Microsoft Analysis Services Client Tools    2005.090.1399.00
Microsoft Data Access Components (MDAC)    6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML    3.0 6.0
Microsoft Internet Explorer    9.0.8112.16421
Microsoft .NET Framework    2.0.50727.5466
Operating System    6.1.7601

Hai sir,

I am using,

Microsoft SQL Server Management Studio  10.50.1600.1

Microsoft Analysis Services Client Tools     2008

Microsoft .NET Framework   3.5

Oerationg System       6.1(7600)

Hmm, I'm not really sure why your version of SQL is acting this way, but if you add the missing "[" bracket in this line (before the "1") it should work:

select EmpId, Ename, 1],[2] from (

I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need to replace it by ‘Y’. For Null values it should be replaced by ‘N’. How can I achieve this? Please help me.

Very slick and useful. Thanks, Greg!

Thanks Greg!. this example is very much clear to understand the usages of SQL cross tab and I was able to build my query based on this.

@Uvarajan: Read about SQL CASE. It will help you.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Great Jobs Greg.Thank's again

Great post, made something very powerful (and something I've previously overlooked) nice and easy to understand

I Have tried the same but not working pls go through my query and let me know what is wrong with my query

And the query is as follows

SELECT SRLNUB,'Trf'AS TRF ,'EVT'AS EVT

FROM

(SELECT SRLNUB, REVCOD, amount

FROM fmtextbl ) ps

PIVOT

(

SUM(amount)

FOR REVCOD IN([TRF],[EVT])

)AS pvt

I Have tried the same but not working pls go through my query and let me know what is wrong with my query

SRLNUB    ACCDAT     REGNUB    ROMNUB    GSTNAM            GSTCLF    MANREV    TRNAMT    REVCOD    CNT    amount 
3221    20130322 26    402    ESPIRITU ANTONINA    3    TRF    183870    TRF    100    33096.6
3215    20130322 39    407    SCOTT VARGAS SUSAN    3    TRF    183870    EVT    100    33096.6


And the query is  as follows 
 SELECT SRLNUB, 'Trf' AS TRF ,'EVT' AS EVT  
FROM 
(SELECT SRLNUB, REVCOD, amount
FROM fmtextbl ) ps
PIVOT
(
SUM (amount)
FOR REVCOD IN ([TRF],[EVT]) 
) AS pvt


ACCDAT        ROMNUB    REGNUB    GSTNAM            GSTCLF    MANREV    TRF         EVT 
20130322    402    26    ESPIRITU ANTONINA           3            TRF         183870    0
20130322    407    39    SCOTT VARGAS SUSAN        3            TRF         0            183870

I am trying same code to implement in PHP but its giving some error.

Table is clientdetails with 3 attributes (clientname,productname,quantity).

<?php

mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("HR") or die(mysql_error());
  
$sql=mysql_query("select clientname, 'Software' as s, 'Desktop' as d
FROM (
select clientname,productname,quantity
FROM clientdetails)
ps pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))") or die("". mysql_error());

Its showing error as

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))' at line 5 ".

Can someone help me in this?

Thanks,

Aditya Singh