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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change column names using xml Path
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/12/2012 :  15:48:48  Show Profile  Reply with Quote
Right now when I run the code below it gives me sales for each month based on the year I'm prompted for.

Is it possible to change the column headings to month+year(Jan2002, Feb2002 ....) Instead of (1,2,3.....)


ALTER PROCEDURE [dbo].[MonthlySales] 
	-- Add the parameters for the stored procedure here
	@YEAR varchar (4)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' +  ltrim(str(month(inv_dt)) )

                        FROM    oehdrhst_sql

                        ORDER BY '],[' + ltrim(str(month(inv_dt)))

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR varchar


SELECT * FROM

      (SELECT cus_no,slspsn_no, Month(inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
            where year(inv_dt)='+CAST(@year as varchar(4))+'

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'

 

execute (@query)
END


visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2012 :  15:55:59  Show Profile  Reply with Quote
replace Month(inv_dt) with LEFT(Datename(mm,inv_dt),3) + datename(yyyy,inv_dt)
in above code on all places

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/12/2012 :  16:21:54  Show Profile  Reply with Quote
Made the changes. Get the following Error now.

Msg 8114, Level 16, State 5, Procedure MonthlySales, Line 22
Error converting data type nvarchar to float.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2012 :  16:30:35  Show Profile  Reply with Quote
show the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/12/2012 :  16:39:14  Show Profile  Reply with Quote
ALTER PROCEDURE [dbo].[MonthlySales]
-- Add the parameters for the stored procedure here
@YEAR varchar (4)


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(str( LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)) )

FROM oehdrhst_sql

ORDER BY '],[' + ltrim(str( LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)))

FOR XML PATH('')

), 1, 2, '') + ']'
SET @query =
'
Declare @YEAR varchar


SELECT * FROM

(SELECT cus_no,slspsn_no, LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt) OrderYear, tot_sls_amt

FROM oehdrhst_sql
where year(inv_dt)='+CAST(@year as varchar(4))+'

) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'



execute (@query)
END
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 06/12/2012 :  16:56:57  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
what datatype is column inv_dt?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/12/2012 :  16:58:57  Show Profile  Reply with Quote
datetime
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 06/12/2012 :  17:32:11  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
which of the columns are of datatype nvarchar ?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/12/2012 :  17:38:46  Show Profile  Reply with Quote
cus_no char20
slspsn_no int
tot_sls_amt decimal 16,2

Only varchar are the variables in the code.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 06/12/2012 :  17:59:52  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
OrderYear has changed from being an int to varchar. could that be the issue. if you could post a few sample data it would help troubleshoot. in your initial post about parameters to this sproc where you were passing @year OrderYear was an int. But now things have changed. that error is misleading. post some oehdrhst_sql sample data

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 06/12/2012 :  19:06:20  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
use this instead


SELECT  @listCol = STUFF(( SELECT DISTINCT

'],[' +  LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)

FROM yeehaw

ORDER BY '],[' +  LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)

FOR XML PATH('')

), 1, 2, '') + ']'


<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2012 :  20:13:17  Show Profile  Reply with Quote
whats the purpose of that variable inside EXEC? if you want to pass a parameter to dynamic sql string why not use sp_exeutesql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lisayling
Starting Member

China
5 Posts

Posted - 06/13/2012 :  05:40:30  Show Profile  Reply with Quote
thanks, this topic is good, i can accept
unspammed

lisayling
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/13/2012 :  09:07:22  Show Profile  Reply with Quote
yosiasz code seemed to kind of work. It lists the column heading like I wanted but the Year parameter seems to be ignored. It goes through and lists sales for every month and year.


I entered 2002 for the year when prompted and my resultes are below.


                cus_no  slspsn  Apr2002 Feb2001 Feb2002 Jan2001 Jan2002 Mar2002 May2002
                  901	10975	NULL	NULL	3369.50	NULL	NULL	NULL	NULL
                 903	10975	NULL	8771.75	NULL	7485.00	11438.00	NULL	34247.95
                 904	10975	2173.63	NULL	5018.50	4385.01	NULL	5018.50	NULL
                 906	10975	NULL	NULL	NULL	NULL	4924.88	6053.00	5153.00
                 908	10975	255.00	NULL	3050.00	1323.10	NULL	NULL	NULL
                 902	11052	NULL	NULL	20291.64	NULL	6702.91	NULL	NULL
                 905	11052	41061.20	NULL	NULL	5181.20	6783.20	12808.60	4193.00
                 907	11052	11968.40	NULL	NULL	1547.00	NULL	7475.00	6340.00


Here is the current code:

ALTER PROCEDURE [dbo].[MonthlySales] 
	-- Add the parameters for the stored procedure here
	@YEAR varchar (4)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
--DECLARE @YEAR numeric
--set @YEAR = @YYEAR
SELECT  @listCol = STUFF(( SELECT DISTINCT

'],[' +  LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)

FROM oehdrhst_sql

ORDER BY '],[' +  LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt)

FOR XML PATH('')

), 1, 2, '') + ']'

SET @query =
'
Declare @YEAR varchar


SELECT * FROM

      (SELECT cus_no,slspsn_no,  LEFT(Datename(month,inv_dt),3) + datename(year,inv_dt) OrderYear, tot_sls_amt

            FROM oehdrhst_sql
            where year(inv_dt)='+CAST(@year as varchar(4))+'

            ) src

PIVOT (SUM(tot_sls_amt) FOR OrderYear

IN ('+@listCol+')) AS pvt'

 

print (@query)
END
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 06/13/2012 :  09:32:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER PROCEDURE	dbo.MonthlySales
(
	@Year SMALLINT
)
AS

SET NOCOUNT ON

SET	@Year = COALESCE(@Year, DATEPART(YEAR, GETDATE()))

DECLARE	@FromDate DATE = DATEADD(YEAR, @Year - 1900, '19000101'),
	@ToDate DATE = DATEADD(YEAR, @Year - 1900, '19010101')

DECLARE @ListCol VARCHAR(1000),
	@Query VARCHAR(MAX)

SET	@ListCol = STUFF((
				SELECT		', ' + QUOTENAME(CONVERT(CHAR(3), MAX(inv_dt), 7) + DATENAME(YEAR, MAX(inv_dt)))
				FROM		dbo.oehdrhst_sql
				WHERE		inv_dt >= @FromDate
						AND inv_dt < @ToDate
				GROUP BY	DATENAME(YEAR, inv_dt),
						DATEPART(MONTH, inv_dt)
				ORDER BY	DATENAME(YEAR, inv_dt),
						DATEPART(MONTH, inv_dt)
				FOR XML		PATH('')
			), 1, 2, '')

SET	@Query ='
			SELECT	p.*
			FROM	(
					SELECT	cus_no,
						slspsn_no,
						CONVERT(CHAR(3), inv_dt, 7) + DATENAME(YEAR, inv_dt) AS OrderYear,
						tot_sls_amt
					FROM	dbo.oehdrhst_sql
					WHERE	inv_dt >= ' + QUOTENAME(CONVERT(CHAR(8), @FromDate, 112), '''') + '
						AND inv_dt < ' + QUOTENAME(CONVERT(CHAR(8), @ToDate, 112), '''') + '
				) AS s
			PIVOT	(
					SUM(s.tot_sls_amt)
					FOR s.OrderYear IN (' + @ListCol +')
				) AS p'

PRINT	@Query
-- EXEC	(@Query)



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 06/13/2012 10:35:11
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/13/2012 :  09:46:11  Show Profile  Reply with Quote
What does that do for me????
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 06/13/2012 :  09:48:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Everything you asked for.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
495 Posts

Posted - 06/13/2012 :  10:22:26  Show Profile  Reply with Quote
You are correct. The first time I looked at the post it only had the first part of the code.

That works great.

What would I need to do if I wanted to prompt for a range? 2010 thru 2012

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/13/2012 :  10:22:29  Show Profile  Reply with Quote
quote:
Originally posted by Vack

What does that do for me????


test and see with your data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 06/13/2012 :  10:37:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER PROCEDURE	dbo.MonthlySales
(
	@FromYear SMALLINT,
	@ToYear SMALLINT
)
AS

SET NOCOUNT ON

SELECT	@FromYear = COALESCE(@FromYear, DATEPART(YEAR, GETDATE())),
	@ToYear = COALESCE(@ToYear, DATEPART(YEAR, GETDATE()))

DECLARE	@FromDate DATE = DATEADD(YEAR, @FromYear - 1900, '19000101'),
	@ToDate DATE = DATEADD(YEAR, @ToYear - 1900, '19010101')

DECLARE @ListCol VARCHAR(1000),
	@Query VARCHAR(MAX)

SET	@ListCol = STUFF((
				SELECT		', ' + QUOTENAME(CONVERT(CHAR(3), MAX(inv_dt), 7) + DATENAME(YEAR, MAX(inv_dt)))
				FROM		dbo.oehdrhst_sql
				WHERE		inv_dt >= @FromDate
						AND inv_dt < @ToDate
				GROUP BY	DATENAME(YEAR, inv_dt),
						DATEPART(MONTH, inv_dt)
				ORDER BY	DATENAME(YEAR, inv_dt),
						DATEPART(MONTH, inv_dt)
				FOR XML		PATH('')
			), 1, 2, '')

SET	@Query ='
			SELECT	p.*
			FROM	(
					SELECT	cus_no,
						slspsn_no,
						CONVERT(CHAR(3), inv_dt, 7) + DATENAME(YEAR, inv_dt) AS OrderYear,
						tot_sls_amt
					FROM	dbo.oehdrhst_sql
					WHERE	inv_dt >= ' + QUOTENAME(CONVERT(CHAR(8), @FromDate, 112), '''') + '
						AND inv_dt < ' + QUOTENAME(CONVERT(CHAR(8), @ToDate, 112), '''') + '
				) AS s
			PIVOT	(
					SUM(s.tot_sls_amt)
					FOR s.OrderYear IN (' + @ListCol +')
				) AS p'

PRINT	@Query
-- EXEC	(@Query)



N 56°04'39.26"
E 12°55'05.63"
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.12 seconds. Powered By: Snitz Forums 2000