Change column names using xml Path

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

SQL Server 2008 Forums

Transact-SQL (2008)

Change column names using xml Path

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 15:48:48

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

52326 Posts

Posted - 2012-06-12 : 15:55:59

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/

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 16:21:54

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 16:30:35

show the code

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

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 16:39:14

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 16:56:57

what datatype is column inv_dt?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 16:58:57

datetime

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 17:32:11

which of the columns are of datatype nvarchar ?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 17:38:46

cus_no char20
slspsn_no int
tot_sls_amt decimal 16,2

Only varchar are the variables in the code.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 17:59:52

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 19:06:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 20:13:17

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/

lisayling
Starting Member

5 Posts

Posted - 2012-06-13 : 05:40:30

thanks, this topic is good, i can accept
unspammed

lisayling

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-13 : 09:07:22

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-13 : 09:32:16

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

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

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-13 : 09:46:11

What does that do for me????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-13 : 09:48:57

Everything you asked for.

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

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-13 : 10:22:26

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:22:29

quote:
Originally posted by Vack

What does that do for me????

test and see with your data

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-13 : 10:37:35

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources