Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
 Posted - 2012-08-14 : 09:15:57
|
Hi Guys,I have a requirements to have the returndate as my header.I use the pivot but i dont know on how to make this as dynamics pivot.I have to get the month to date data.THank you in advance.Here is my sample data.Create table #SampleData(Returndate datetime,Itemid nvarchar(35),Qty int)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-06','HTC1201',100)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-06','LGE3539',200)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-13','HTC1201',150)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-13','LGE3539',100)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-20','HTC1201',400)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-20','LGE3539',400)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-27','HTC1201',100)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-07-27','LGE3539',100)Insert into #SampleData (Returndate, Itemid, Qty) values ('2012-08-03','HTC1201',1000) Result:Itemid----6----13-------20-----27---------------------------------HTC1201--100---150-----400----100LGE3539--200---100-----400----100My Script:SELECT *FROM (SELECT Itemid, DAY(ReturnDate) AS trandate, Qty FROM #SampleData) AS D PIVOT(SUM(qty) FOR trandate IN([6],[13],[20],[27])) AS POrder by Itemid |
|
|
YuvarajKrishna
Starting Member
4 Posts |
Posted - 2012-08-14 : 09:47:51
|
| Hi Villanuev Try This its will work DECLARE @Columns VARCHAR(max)DECLARE @DynamicSql VARCHAR(max)Select @Columns = +Coalesce(+@Columns +'],[','') + ReturnDate From ( SELECT DISTINCT CONVERT(VARCHAR,ReturnDate,101) ReturnDate FROM #SampleData )ASET @DynamicSql ='SELECT * FROM (SELECT Itemid, convert(varchar,ReturnDate,101) AS trandate, Qty FROM #SampleData ) AS D PIVOT(SUM(qty) FOR trandate IN(['+@Columns+'])) AS POrder by Itemid'EXEC (@DynamicSql)Yuvaraj |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2012-08-14 : 18:36:34
|
| Just had a quick comment about this post and a question. Wouldnt it be easier to use a matrix to do this dynamic pivot? I'm not sure if you have access to reporting services, but it seems like this could be done in a matrix by using column groups. If you dont have access to reporting services, then this code works fine, but your life would be much easier. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-14 : 18:42:09
|
quote:
Originally posted by joe8079
Just had a quick comment about this post and a question. Wouldnt it be easier to use a matrix to do this dynamic pivot? I'm not sure if you have access to reporting services, but it seems like this could be done in a matrix by using column groups. If you dont have access to reporting services, then this code works fine, but your life would be much easier.
yep..if you're using SSRS as front end matix container would do the trick for you. Otherwise its best dealt with inside T-SQL using a function like in the link posted------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2012-08-15 : 01:31:46
|
| Thank you guys for the reply.Hi Visakh & Joe8079,Yes, i have an access to reporting services. actually my objective for this script is to use in SSRS report.I will make a dataset using this scripts that will create a report in SSRS.btw, youre saying about the matrix that do a dynamic pivot, can you please give me some idea on this. thanks. |
|
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2012-08-15 : 17:32:59
|
| Hey, its actually really, really easy to use a matrix container for what your doing. Basically, you would take whatever field you wanted to pivot on and add it as a column group. It works just like the dynamic pivot in sql, but much easier to work with and you can add some more detail to the report as well. You have row groups, column groups and a field with what you want to have as an aggregate. here is a pretty good link. It does everything your trying to do in reporting services. http://technet.microsoft.com/en-us/library/ms157334%28v=sql.100%29.aspx |
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2012-08-16 : 05:42:23
|
| Thank you very much @Joe8079. it working now. its easier compare to dynamics pivot. very helpful.. |
|
|
|
|
|
|
|
|
Dynamic Pivot