pivot in sql server 2005 - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

pivot in sql server 2005

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2013-06-12 : 02:55:39

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
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)t
PIVOT (MAX(Weight) FOR MonthYear IN ([JAN2012],[Mar2012],[Jan2013],[Feb2013],[March2013],[April2013]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 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

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

Posted - 2013-06-12 : 03:33:27

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 03:50:37

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

asif372
Posting Yak Master

100 Posts

Posted - 2013-06-14 : 03:49:55

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

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 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)
[/code]

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-14 : 06:30:40

asif sen bakidansan?

you is from baku?

kmkmmm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources