SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot in sql server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 06/12/2013 :  02:55:39  Show Profile  Reply with Quote
Pivoting matter

my data is like this
clientID---item no-----month----year----weight
102--------1162702------JAN----2012----23685
102---------12060001-----JAN----2012----1987
102---------1162702------mar----2012----2156
102---------12060001-----mar----2012----1952
102---------1162702-----JAN----2013----21600
102---------12060001-----JAN---2013----19800
102---------12060003-----FEB---2013----1017.5
102---------12060005-----FEB---2013----64260
102---------12060006-----march---2013----46920
102---------12060008-----MARCH---2013----22050
102---------12080001-----April---2013----750
102---------12080001-----April---2013----1950

mr required result is like thias

clientID--item no-----JAN2012--mar2012--jan2013--Feb2013--March2013--April2013
102-------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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  02:59:07  Show Profile  Reply with Quote
You can PIVOT query for this kind of result

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  02:59:17  Show Profile  Reply with Quote

SELECT *
FROM (SELECT clientID,[item no],month + cast(year as varchar(4)) as MonthYear, weight
      FROM table)t
PIVOT (MAX(Weight) FOR MonthYear IN ([JAN2012],[Mar2012],[Jan2013],[Feb2013],[March2013],[April2013]))p



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  03:03:09  Show Profile  Reply with Quote
for making it dynamic use like


DECALRE @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)t
PIVOT (MAX(Weight) FOR MonthYear IN (' + @MonthYearList + '))p'

EXEC(@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  03:22:38  Show Profile  Reply with Quote
You can use ISNULL()/ COALESCE() scalar functions to convert NULLs as zeros....

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 06/12/2013 :  03:33:27  Show Profile  Reply with Quote
You can understand the concept of PIVOT by using this link
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/12/2013 :  03:50:37  Show Profile  Reply with Quote
And use this approach if you need to return the total values also in pivot

http://visakhm.blogspot.in/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 06/14/2013 :  03:49:55  Show Profile  Reply with Quote
Thanks for your Responce
above Suggestions are working up to the demand

my data is like this
clientID---item no-----month----year----weight
102--------1162702------JAN----2012----23685
102---------12060001-----JAN----2012----1987
102---------1162702------mar----2012----2156
102---------12060001-----mar----2012----1952
102---------1162702-----JAN----2013----21600
102---------12060001-----JAN---2013----19800
102---------12060003-----FEB---2013----1017.5
102---------12060005-----FEB---2013----64260
102---------12060006-----march---2013----46920
102---------12060008-----MARCH---2013----22050
102---------12080001-----April---2013----750
102---------12080001-----April---2013----1950

now i want selected month no just like if user select July 2012 till Feb 2013
then no of selected month should be displayed in front like this

MONTH---YEAR-------Selected MOnth
JULY----2012----------------1
Aug-----2012----------------2
Sept----2012----------------3
Oct-----2012----------------4
Nov-----2012----------------5
Dec-----2012----------------6
Jan-----2013----------------7
Feb-----2013----------------8

how can it be possible
Thanks
Kind Regards
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/14/2013 :  04:00:51  Show Profile  Reply with Quote

DECLARE @StartMonth varchar(20),@EndMonth varchar(20) -- variables to show user inputs

SELECT @StartMonth = 'Jul 2012',@EndMonth = 'Feb 2013'

;With Month_CTE
AS
(
SELECT CONVERT(datetime, '01' + @StartMonth,106) AS Dateval,1 AS Cnt
UNION ALL
SELECT DATEADD(mm,1,Dateval),Cnt+1
FROM Month_CTE
WHERE DATEADD(mm,1,Dateval) <= '01' + @EndMonth
)

SELECT *
FROM Month_CTE
OPTION (MAXRECURSION 0)


Make sure you pass values as MMM YYYY format for start and end fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
139 Posts

Posted - 06/14/2013 :  06:30:40  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
asif sen bakidansan?

you is from baku?

kmkmmm
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000