Author |
Topic |
asif372
Posting Yak Master
100 Posts |
Posted - 2013-06-12 : 02:55:39
|
Pivoting mattermy data is like thisclientID---item no-----month----year----weight102--------1162702------JAN----2012----23685102---------12060001-----JAN----2012----1987102---------1162702------mar----2012----2156102---------12060001-----mar----2012----1952102---------1162702-----JAN----2013----21600102---------12060001-----JAN---2013----19800102---------12060003-----FEB---2013----1017.5102---------12060005-----FEB---2013----64260102---------12060006-----march---2013----46920102---------12060008-----MARCH---2013----22050102---------12080001-----April---2013----750102---------12080001-----April---2013----1950mr required result is like thiasclientID--item no-----JAN2012--mar2012--jan2013--Feb2013--March2013--April2013102-------1162702----23685---0--------0--------0----------0----------0--------102-------12060001---1987----0--------0--------0----------0----------0--------102-------1162702----0-------2156-----0--------0----------0----------0--------102-------12060001---0-------1952-----0--------0----------0----------0--------102-------1162702----0-------0--------21600----0----------0----------0--------102-------12060001---0-------0--------19800----0----------0----------0--------102-------12060003---0-------0--------0--------1017.5-----0----------0--------102-------12060005---0-------0--------0--------64260------0----------0--------102-------12060006---0-------0--------0--------0----------46920------0--------102-------12060008---0-------0--------0--------0----------22050------0--------102-------12080001---0-------0--------0--------0----------0----------750------102-------12080001---0-------0--------0--------0----------0----------1950-----Thanks in Advance |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 02:59:07
|
You can PIVOT query for this kind of result--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 02:59:17
|
[code]SELECT *FROM (SELECT clientID,[item no],month + cast(year as varchar(4)) as MonthYear, weight FROM table)tPIVOT (MAX(Weight) FOR MonthYear IN ([JAN2012],[Mar2012],[Jan2013],[Feb2013],[March2013],[April2013]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 03:03:09
|
for making it dynamic use likeDECALRE @MonthYearList varchar(2000),@SQL varchar(max)SELECT @MonthYearList = STUFF((SELECT DISTINCT ',[' + month + cast(year as varchar(4)) + ']' FROM Table ORDER BY ',[' + month + cast(year as varchar(4)) + ']' FOR XML PATH('')),1,1,'')SELECT @SQL='SELECT clientID,[item no],' + @MonthYearList +'FROM (SELECT clientID,[item no],month + cast(year as varchar(4)) as MonthYear, weight FROM table)tPIVOT (MAX(Weight) FOR MonthYear IN (' + @MonthYearList + '))p'EXEC(@SQL) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 03:22:38
|
You can use ISNULL()/ COALESCE() scalar functions to convert NULLs as zeros....--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-06-14 : 03:49:55
|
Thanks for your Responceabove Suggestions are working up to the demandmy data is like thisclientID---item no-----month----year----weight102--------1162702------JAN----2012----23685102---------12060001-----JAN----2012----1987102---------1162702------mar----2012----2156102---------12060001-----mar----2012----1952102---------1162702-----JAN----2013----21600102---------12060001-----JAN---2013----19800102---------12060003-----FEB---2013----1017.5102---------12060005-----FEB---2013----64260102---------12060006-----march---2013----46920102---------12060008-----MARCH---2013----22050102---------12080001-----April---2013----750102---------12080001-----April---2013----1950now i want selected month no just like if user select July 2012 till Feb 2013then no of selected month should be displayed in front like thisMONTH---YEAR-------Selected MOnthJULY----2012----------------1Aug-----2012----------------2Sept----2012----------------3Oct-----2012----------------4Nov-----2012----------------5Dec-----2012----------------6Jan-----2013----------------7Feb-----2013----------------8how can it be possibleThanksKind Regards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 04:00:51
|
[code]DECLARE @StartMonth varchar(20),@EndMonth varchar(20) -- variables to show user inputsSELECT @StartMonth = 'Jul 2012',@EndMonth = 'Feb 2013';With Month_CTEAS(SELECT CONVERT(datetime, '01' + @StartMonth,106) AS Dateval,1 AS CntUNION ALLSELECT DATEADD(mm,1,Dateval),Cnt+1FROM Month_CTEWHERE DATEADD(mm,1,Dateval) <= '01' + @EndMonth)SELECT *FROM Month_CTEOPTION (MAXRECURSION 0)[/code]Make sure you pass values as MMM YYYY format for start and end fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-14 : 06:30:40
|
asif sen bakidansan?you is from baku?kmkmmm |
|
|
|