Solved

Dynamic Crosstab query in SQL Server 2005

Posted on 2010-09-10
6
448 Views
Last Modified: 2012-05-10
I'm trying to create a dynamic crosstab query in server 2005. I copied this codes from a book and modified it to my purpose, but I couldn't get it to work. I got the error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. My crosstab columns is a datetime field. Here's is the codes, please help:

DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''

SELECT @XColumns = @XColumns + [a].[Column] + ', '
      FROM
            (SELECT DISTINCT [Date] AS [Column]
                  FROM TRP.CAP_PHID) AS a

PRINT @XColumns
SET @XColumns = Left(@XColumns, LEN(@XColumns) - 1)

SET @XColumns = 'SELECT Tab, PhID, ProdLine, Segment, '
      + @XColumns
      + ' FROM TRP.CAP_PHID PIVOT (SUM (FullProj) FOR [Date] IN ('
      + @XColumns
      + ')) AS pt'
PRINT @XColumns

EXEC sp_executesql @XColumns
0
Comment
Question by:HNA071252
  • 3
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33649813
may I suggest this code instead?

DECLARE @strSQL VARCHAR(MAX) 

DECLARE @COLS NVARCHAR(2000) 

  

SELECT @COLS = STUFF((SELECT  distinct

                                        '], [' + convert(varchar, [Date], 112)

                                FROM TRP.CAP_PHID order by 1

                                FOR XML PATH('') 

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





set @strSQL = 'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols

      + ' FROM TRP.CAP_PHID 

	  PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + ')) AS pt'





EXEC sp_executesql @strSQL

Open in new window

0
 

Expert Comment

by:A071252
ID: 33650278
How do I use this code? I executed in the SQL Server window and I got this message:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 33650354
just add N' there

DECLARE @strSQL NVARCHAR(MAX) 

DECLARE @COLS NVARCHAR(2000) 

  

SELECT @COLS = STUFF((SELECT  distinct

                                        '], [' + convert(varchar, [Date], 112)

                                FROM TRP.CAP_PHID order by 1

                                FOR XML PATH('') 

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





set @strSQL = N'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols

      + N' FROM TRP.CAP_PHID 

	  PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + N')) AS pt'





EXEC sp_executesql @strSQL

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Expert Comment

by:A071252
ID: 33650434
Great! It works. How do I change to show the column as "200901" (instead of 20090101, etc.)? Also how do I put this into a view so that I could link to it from Access?
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 33650507
>>Also how do I put this into a view so that I could link to it from Access? <<
you can't.
What you will need to to is create a stored procedure and call the SP from access.
>>How do I change to show the column as "200901" (instead of 20090101, etc.)?  <<
So you just want to show the month and not all days? Try the below:

DECLARE @strSQL NVARCHAR(MAX) 

DECLARE @COLS NVARCHAR(2000) 

  

SELECT @COLS = STUFF((SELECT  distinct

                                        '], [' + convert(varchar(6), [Date], 112)

                                FROM TRP.CAP_PHID order by 1

                                FOR XML PATH('') 

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





set @strSQL = N'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols

      + N' FROM (SELECT *, convert(varchar(6), [Date]) as dat TRP.CAP_PHID ) o

	  PIVOT (SUM (FullProj) FOR dat IN (' + @Cols + N')) AS pt'





EXEC sp_executesql @strSQL

Open in new window

0
 

Author Closing Comment

by:HNA071252
ID: 33650717
Great help, Thank you very much!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now